Next level of multi-leveled formulas - array formula maybe?

See attached sample

The math itself is cake - the best (fastest) way to 'apply' it to 1272 rows of data is the complicated side....

I need the answers in K, N, P and Q

C is the total number of hours
Of the hours in C, I need to know how many were completed by Elmo Monster in B with the answer in L
Of the hours in C, I need to know how many were completed by Ernie Muppet in B with the answer in N

Then in P, I need to know how many of the total hours were not completed by Elmo or Ernie (so total - Elmo - Ernie)
Then in Q, I need to know how many of the total shifts (O is the total number of shifts) were not completed by Elmo or Ernie (so total - Elmo - Ernie) Respectively K and M

Keeping in mind that my original data file has 1272 lines of data and the number of rows within each subtotal group varies from 1 to 100 or more.

I really liked the array formula that was provided for column D last week, but for the life of me cannot figure out how to modify it for what I am now trying to accomplish.

Thanks

I've attached a solution for you. You can copy and paste these formulas in cells K2:Q2 and drag them down. They will only appear in the "Total" rows, like with column D.

I've removed the conditional formatting from columns K and M because they weren't working how I think they should.

Formulas from K2 through Q2 in that order:

Please Login or Register  to view this content.

No need for array formulas on this one! Obviously you'll need to replace your example names with the true Clients and Caregivers, which is fairly straightforward in these formulas.

Hope that helps!

Excellent ThirdFret - The =D2 isn't going to work because there are more shifts than number of days served (some days had multiple shifts), but that is ok, because that formula was already worked out. Thank you so much for helping me get this working!

C