Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal on SumIf | Excel Worksheet Functions | |||
Can SUMIF function include AND function | Excel Worksheet Functions | |||
Subtotal Bug in Excel 2003 | Excel Discussion (Misc queries) | |||
Combining SUMIF and SUBTOTAL functions | Excel Worksheet Functions | |||
Can you combined the SUMIF and SUBTOTAL functions in a formula? | Excel Worksheet Functions |