1

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

  • Hi and welcome on Web Applications !! Can you make us a dummy sheet so that we can work with the data you provided. The expected outcome is certainly carefully explained. – Jacob Jan Tuinstra Nov 29 '17 at 11:49
  • Here is the dummy sheet. I removed any names and client names from the different month's sheets for privacy's sake, but that shouldn't change what I am going for at all. The sheet I'm working on is the "Data" Sheet. docs.google.com/spreadsheets/d/… – DBirch Nov 30 '17 at 1:14
1

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

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

SUMIF

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.