excel 2016 combine subtotal with sumifs

0
votes

My SUMIFS needs to add up column D (D11:D172) and the first criteria is a range called qbplistcomp (which is column B so B11:B172) for multiple hits (e.g. CHF, COPD, and all STROKE so using STROKE*) and the second criteria is in column C (C11:C172) looking for the criteria which is a label "qbp volumes" ... so far without subtotaling, this formula works as:

=SUM(SUMIFS(D11:D172,qbplistcomp,{"CHF","COPD","STROKE*"},C11:C172,"QBP Volumes")) Now my column A has the ability to filter on Category, so if I choose say just "stroke" it should only subtotal (sum) just those ones ... keep getting error ... help, thanks, Tina

1
excelformulasumifssubtotal
Welcome to SO.. I am not sure but it looks like you want both the functions to work - Sumif when you dont filter the column A and conditional total when you filter Column A.... This may help ....exceljet.net/formula/count-visible-rows-only-with-criteria .. Instead of count use sum...Ok I guess you will have to work on it.. You cant use it directly. - Naresh
Also, youtube.com/watch?v=lzjc_eEISe8 - Naresh

1 Answers

0
votes

Interesting question.. Why? It gives us a very multidimensional fomula combining SumIfs and Subtotal.

My examples formula.. =SUMPRODUCT((A6:A17=A2)*(B6:B17={"North","West","East"})*(SUBTOTAL(103,OFFSET(B6,ROW(B6:B17)-MIN(ROW(B6:B17)),0)))*(C6:C17))

And hence your formula should be SUMPRODUCT((C11:C172="QBP Volumes")*(B11:B172={"CHF","COPD","STROKE*"})*(SUBTOTAL(103,OFFSET(B11,ROW(B11:B172)-MIN(ROW(B11:B172)),0)))*(D11:D172))

See When the B Col is NOT filtered for "North" C2= 38859 i.e. total for A in North and West enter image description here

When the B Col is filtered for "North" C2= 32313 i.e. total for A ONLY in North enter image description here

Remember, subtotal formula is applicable to the column you are supposed to filter(Col B). If you intend the same for Col c then replace(C11:C172="QBP Volumes") with subtotal formula so your new formula will be SUMPRODUCT((SUBTOTAL(103,OFFSET(C11,ROW(C11:C172)-MIN(ROW(C11:C172)),0)))*(B11:B172={"CHF","COPD","STROKE*"})*(SUBTOTAL(103,OFFSET(B11,ROW(B11:B172)-MIN(ROW(B11:B172)),0)))*(D11:D172))