Using COUNTIF (or SUMIF) with SUBTOTAL to only sum up numbers that meet certain criteria

[jcwhite999]

I need some help with a complex combination of Excel functions. I routinely use SUBTOTAL to sum up values for a filtered list. I also routinely use COUNTIF/SUMIF to look at a full list of data and sum up values that meet certain criteria. I want to combine these two functionalities. Here is an example:

Program Resource Cost Type Dollars
A Jim Full-Time $100
A Mary Full-Time $200
A Pete Part-Time $300
B Jim Part-Time $400
B Mary Full-Time $500
B Pete Part-Time $600

I can use SUBTOTAL to sum up all the dollars for Program A ($100+$200+$300=$600). However, I want to separate the sum of values for the Full-Time people on Program A ($100+$200=$300) and the Part-Time people on Program A ($300). For example, my output might look like this:

(This would be my two formulas above all the data that would use the SUBTOTAL functions)
Full-Time: $300
Part-Time: $300

Program Resource Cost Type Dollars <---- I would have filters on this row and select Program A
A Jim Full-Time $100
A Mary Full-Time $200
A Pete Part-Time $300

Any help or guidance would be greatly appreciated. I'm not a super-user, so combinations like this are not in my wheelhouse.

Thanks,
Chris

 

[Aladin Akyurek]

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(D2,ROW(D2:D7)-ROW(D2),0,1)),--(A2:A7="A"),--(C2:C7="Part-Time"))

 

[Jonmo1]

I routinely use SUBTOTAL to sum up values for a filtered list. I also routinely use COUNTIF/SUMIF to look at a full list of data and sum up values that meet certain criteria

Since you used the word 'Routinely' twice, i would suggest using a helper column for the subtotal bit.
Say column E
In E2 and filled down put
=SUBTOTAL(103,A2)

Then use

=SUMIFS(D2:D7,A2:A7,"A",C2:C7,"Part-Time",E2:E7,1)