How to combine Subtotal, Sumifs and more

One potential workaround is to use SUBTOTAL for each value of K.

In another column, let's say Z, put =SUBTOTAL(9,K2) and drag down. Now build the SUMIFS on column Z instead, and Filter against column Z.

This works because SUBTOTAL = 0 when it itself is not visible.

Try something like this.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AnalyseData!$K2,ROW(AnalyseData!$K2:$K100)-ROW(AnalyseData!$K2),0)),--(AnalyseData!$F2:$F100='New Table'!$D$12),--(AnalyseData!$D2:$D100='New Table'!$B$2),--(AnalyseData!$D2:$D100='New Table'!$B$3))

You should avoid using entire columns as range references in array formulas and the SUMPRODUCT function. Use smaller specific ranges.

Well, it'll work but it might take a second or two to calculate.

I just noticed this but should have caught it earlier...

Column D in AnalyseData = Cell B2 in New Table
Column D in AnalyseData = Cell B3 in New Table

Column D in AnalyseData can not = both Cell B2 and B3 in New Table at the same time.

In other words, if you have a test for each:

--(AnalyseData!$D2:$D100='New Table'!$B$2),--(AnalyseData!$D2:$D100='New Table'!$B$3)

The result will always be 0 since both tests will never be true at the same time.

Originally Posted by Tipler93

Would there be a way to include this? Possibly as an IF statement?

Do you mean to test the column for an "OR" condition?

Column D in AnalyseData = B2 OR B3 in New Table

You're welcome.


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.