Include Subtotal in SumIf
 

I would need to sum a range of cells that match a certain condition (SUMIF),
but I would need it to work as well being limited by the autofilter... anyone
to help?

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
ange=Criterion),RangeToSum)

Hope this helps!

In article ,
Bonobo wrote:

I would need to sum a range of cells that match a certain condition (SUMIF),
but I would need it to work as well being limited by the autofilter... anyone
to help?

1. I am really gratefull for the help, it works!
2. I am really frustrated as I don't understand the formula...

"Domenic" wrote:

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Range,ROW(Range)-MIN(ROW(Range)),0,1)),--(R
ange=Criterion),RangeToSum)

Hope this helps!

In article ,
Bonobo wrote:

I would need to sum a range of cells that match a certain condition (SUMIF),
but I would need it to work as well being limited by the autofilter... anyone
to help?

In article ,
Bonobo wrote:

1. I am really gratefull for the help, it works!
2. I am really frustrated as I don't understand the formula...

Let's assume that A1:C6 contains your data, and that the data is
filtered for 'x' with the following result...

Row 1 Label1 Label3 Label2
Row 2 x red 10
Row 4 x red 12
Row 6 x blue 15

If we have the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),--(B
2:B6="Red"),C2:C6)

SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) evaluates to:

{1;0;1;0;1}

Visible cells containing data are assigned 1 and hidden cells are
assigned 0.

--(B2:B6="Red") evaluates to:

{1;0;1;1;0}

Each cell is evaluated as TRUE or FALSE, which is then coerced by the
double negative '--' into its numerical equivalent of 1 and 0,
respectively.

C2:C6 evaluates to:

{10;16;12;18;15}

SUMPRODUCT then multiplies the evaluations...

{10;0;12;0;0}

....which it sums, and returns 22.

Hope this helps!

Include Subtotal in SumIf
 

Hi Domenic, I had the same problem of Bonobo and I tried the formula below,
but I had the following problem:

ex:
Row 1 Label1 Label3 Label2
Row 2 x red 10
Row 3 y green 5
Row 4 x red 12
Row 5 y red 30
Row 6 x blue 15

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1))*(B2:B6="Red")*(C2:C6)) --52 (which is fine)

But if I filter Label 1 for "X", the result is still 52 and not 22. And if I
filter "Y" gives 0 instead of 30.

How could I get it work?
Thanks a lot in advance!
Antonio


"Domenic" wrote:

In article ,
Bonobo wrote:

1. I am really gratefull for the help, it works!
2. I am really frustrated as I don't understand the formula...

Let's assume that A1:C6 contains your data, and that the data is
filtered for 'x' with the following result...

Row 1 Label1 Label3 Label2
Row 2 x red 10
Row 4 x red 12
Row 6 x blue 15

If we have the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),--(B
2:B6="Red"),C2:C6)

SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) evaluates to:

{1;0;1;0;1}

Visible cells containing data are assigned 1 and hidden cells are
assigned 0.

--(B2:B6="Red") evaluates to:

{1;0;1;1;0}

Each cell is evaluated as TRUE or FALSE, which is then coerced by the
double negative '--' into its numerical equivalent of 1 and 0,
respectively.

C2:C6 evaluates to:

{10;16;12;18;15}

SUMPRODUCT then multiplies the evaluations...

{10;0;12;0;0}

....which it sums, and returns 22.

Hope this helps!

Include Subtotal in SumIf
 

I found the answer myself: i was using the italian version of office and
translated the function ROW() with RIGA(), while it's RIF.RIGA()

With this substitution, works perfectly!

Thanks a lot!

PS: i added this comment because it might be useful for some italian looking
for the same answer

"Domenic" wrote:

In article ,
Bonobo wrote:

1. I am really gratefull for the help, it works!
2. I am really frustrated as I don't understand the formula...

Let's assume that A1:C6 contains your data, and that the data is
filtered for 'x' with the following result...

Row 1 Label1 Label3 Label2
Row 2 x red 10
Row 4 x red 12
Row 6 x blue 15

If we have the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),--(B
2:B6="Red"),C2:C6)

SUBTOTAL(3,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)) evaluates to:

{1;0;1;0;1}

Visible cells containing data are assigned 1 and hidden cells are
assigned 0.

--(B2:B6="Red") evaluates to:

{1;0;1;1;0}

Each cell is evaluated as TRUE or FALSE, which is then coerced by the
double negative '--' into its numerical equivalent of 1 and 0,
respectively.

C2:C6 evaluates to:

{10;16;12;18;15}

SUMPRODUCT then multiplies the evaluations...

{10;0;12;0;0}

....which it sums, and returns 22.

Hope this helps!