Sumproduct/Subtotal Formula Tweak

[dcuellar]

I have a formula that works for the sum of visible rows in one column if criteria in another column is met. So, like sumif on visible cells of a filtered set of data.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Monthly_Detail_Instantis!DX$2,ROW(Monthly_Detail_Instantis!DX$2:Monthly_Detail_Instantis!DX273)-ROW(Monthly_Detail_Instantis!DX$2),0)),--(Monthly_Detail_Instantis!D$2:Monthly_Detail_Instantis!D273=H9))

My issue is I want to add more than one column. I want to specify a range of columns. Instead of adding DX I want to add Columns DX, DY, DZ, etc. I know that I could add that formula to itself and modify for each column, but I was hoping for a better solution.

 

[sheetspread]

(wait hang on...)

 

[sheetspread]

Are the rows hidden or filtered? Can you use those conditions in the sumproduct formula?

 

[dcuellar]

filtered.

 

[sheetspread]

It might be better to add those filter conditions to the sumproduct instead, what do you think?

 

[dcuellar]

I found that there were many different solutions.

This works.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Monthly_Detail_Instantis!CE$2[COLOR=#FF0000]:Monthly_Detail_Instantis!ED$2[/COLOR],ROW(Monthly_Detail_Instantis!CE$2[COLOR=#FF0000]:Monthly_Detail_Instantis!ED273[/COLOR])-ROW(Monthly_Detail_Instantis!CE$2[COLOR=#FF0000]:Monthly_Detail_Instantis!ED$2[/COLOR]),0)),--(Monthly_Detail_Instantis!D$2:Monthly_Detail_Instantis!D273=H9))

This works.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Monthly_Detail_Instantis!CE$2:M[COLOR=#FF0000]onthly_Detail_Instantis!ED$2,R[/COLOR]OW(Monthly_Detail_Instantis!CE$2[COLOR=#FF0000]:Monthly_Detail_Instantis!ED273[/COLOR])-ROW(Monthly_Detail_Instantis!CE$2),0)),--(Monthly_Detail_Instantis!D$2:Monthly_Detail_Instantis!D273=H9))

This works.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Monthly_Detail_Instantis!CE$2[COLOR=#FF0000]:Monthly_Detail_Instantis!ED$2[/COLOR],ROW(Monthly_Detail_Instantis!CE$2)-ROW(Monthly_Detail_Instantis!CE$2),0)),--(Monthly_Detail_Instantis!D$2:Monthly_Detail_Instantis!D273=H9))

To gain a better understanding I'd like to know why each of these work and if there is anything I should know about these that may give me incorrect data or undesired results.

 

[sheetspread]

See here if you haven't already:

http://www.mrexcel.com/forum/excel-questions/567747-sum-product-subtotal-offset-function-2.html

Arrays with Offset | Daily Dose of Excel


The pivot table might be an even better solution for you, it can filter and sum too (and faster)