Combine Sumif and Subtotal
 

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)))

Combine Sumif and Subtotal
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
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)))

Combine Sumif and Subtotal
 

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



"T. Valko" wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
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)))

Combine Sumif and Subtotal
 

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
--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
That's work perfectly well.
But can you breifly explain the formlar?
It is so long and complicated.



"T. Valko" wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
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)))

Combine Sumif and Subtotal
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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
--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
That's work perfectly well.
But can you breifly explain the formlar?
It is so long and complicated.



"T. Valko" wrote:
Try this:

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

--
Biff
Microsoft Excel MVP


"Flu" wrote in message
...
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)))