Combine Sumif and Subtotal

F

Flu

Try to combine a sumif in a subtotal row.
But got #VALUE! instead.
Can someone advise what's wrong with this formular?

=SUMPRODUCT((ISNUMBER(MATCH($E$5:$E$10,$E$8,0))),(SUBTOTAL(9,F$5:F$10)))

or can I use a simpler one
=sumif($E$5:$E$10,$E$8,(subtotal(9,F5:F10)))
 
T

T. Valko

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E5:E10,ROW(E5:E10)-ROW(E5),0,1)),--(E5:E10=E8),F5:F10)
 
F

Flu

That's work perfectly well.
But can you breifly explain the formlar?
It is so long and complicated.
 
T

T. Valko

Ok, let's see....

The SUBTOTAL functions are designed to work on filtered data. However, they
aren't designed to work based on conditional requirements. So, we have to
"trick" SUBTOTAL into doing what we want it to do and use it to arrive at a
conditional sum or SUMIF.

SUBTOTAL aggregates its calculation per row and then returns the result of
that aggregation. We need to get SUBTOTAL to calculate each row and return
an individual result for each row instead of an aggregated result. We do
this with the OFFSET function.

When the filter is not applied (show all), the result of each iteration of
SUBTOTAL will be 1 (provided there are no empty cells within the range).
When the filter is applied those rows that are filtered will will return a
result of 0. This array of 1s and 0s is then multiplied together with the
other conditional test and the values we want to sum.

The conditional test: (E5:E10=E8), will return an array of TRUE or FALSE.
These get coerced into 1s and 0s by using the double
unary: --. --(E5:E10=E8)

So, we end up with a matrix like this when the range is unfiltered. The 1st
column is the array of SUBTOTAL results. The 2nd column is the array
of --(E5:E10=E8) results and the 3rd column are the values we want to sum.

1...1...5
1...1...7
1...0...3
1...1...1
1...0...5

Now, when we apply the filter the matrix may look like this. The only thing
that changes is the first column which is our array of SUBTOTALS. A 0 in the
first column of the matrix indicates that that row has been filtered out of
the range.

0...1...5
1...1...7
0...0...3
0...1...1
1...0...5

So, these columns are then multiplied together and summed to arrive at our
desired result:

Unfiltered:

1...1...5 = 1*1*5 = 5
1...1...7 = 1*1*7 = 7
1...0...3 = 1*0*3 = 0
1...1...1 = 1*1*1 = 1
1...0...5 = 1*0*5 = 0

Result = 13

Filtered:

0...1...5 = 0*1*5 = 0
1...1...7 = 1*1*7 = 7
0...0...3 = 0*0*3 = 0
0...1...1 = 0*1*1 = 0
1...0...5 = 1*1*5 = 0

Result = 7


exp101
 
T

T. Valko

Ooops...one minor typo at the very end of the post:
Filtered:
0...1...5 = 0*1*5 = 0
1...1...7 = 1*1*7 = 7
0...0...3 = 0*0*3 = 0
0...1...1 = 0*1*1 = 0
1...0...5 = 1*1*5 = 0

That last line should be:

1...0...5 = 1*0*5 = 0
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top