Google-sheets – Getting total number of identical items from multiple columns

formulasgoogle sheets

My goal is to find out the total number of each type of shift for the year.

I have each type of shift calculated out per month in their respective month columns:

I got the list of unique shifts for each month by using this formula for drawing the unique shifts from the month's sheet:

=unique(Jan!E$4:E)

enter image description here

Then I got the total number of of the unique shifts for each month by using this formula where all the A cells had a 1 to count for the shift:

=SUMIF(Jan!E$4:E$100,C24,Jan!A$4:A$100)

enter image description here
enter image description here

I then got the accumulated unique list from all the months by using this formula shown in the photo below:

=unique(query({C$24:C24;E$24:E31;G$24:G25;I$24:I27;K$24:K33;M$24:M32;O$24:O33;Q$24:Q33;S$24:S34;U$24:U33;W$24:W33;Y$24:Y33}))

enter image description here

Now my final task is to get a formula that will take all the respective numbers for all the unique shifts listed out by month and get the total numbers for each unique shift type for the yearly count. How do I do that? If you have the answer, please explain why as I am trying to understand the concept behind it. Thank you so much.

enter image description here

Best Answer

I think what you want is in Data!B24 and copied down to suit:

=sumif(C:Y,A24,D:Z)

SUMIF

Related Topic