Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bonobo
 
Posts: n/a
Default 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?
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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?

  #3   Report Post  
Bonobo
 
Posts: n/a
Default

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?


  #4   Report Post  
Domenic
 
Posts: n/a
Default

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!
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 134
Default 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!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 134
Default 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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subtotal on SumIf Steven Excel Worksheet Functions 1 May 26th 05 12:25 AM
Can SUMIF function include AND function ShaneS Excel Worksheet Functions 1 May 17th 05 03:24 AM
Subtotal Bug in Excel 2003 GON Excel Discussion (Misc queries) 2 May 10th 05 08:42 PM
Combining SUMIF and SUBTOTAL functions [email protected] Excel Worksheet Functions 1 April 22nd 05 06:14 AM
Can you combined the SUMIF and SUBTOTAL functions in a formula? [email protected] Excel Worksheet Functions 1 April 22nd 05 04:05 AM


All times are GMT +1. The time now is 10:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"