Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function with an If criteria
Hello,
If the following is example data: A B C 1 Person $ Selected 2 Tom 10000 Y 3 Jack 15000 Y 4 Tom 20000 N 5 Tom 1000 Y 6 Jack 5000 N 7 8 51000 And cell B8 has the Subtotal(9,b2:b6) function, so I can sum based on a filter on column A, is there a way to include a criteria in this subtotal where Column C (selected) is Y? |
#2
|
|||
|
|||
Answer: Subtotal function with an If criteria
Yes, you can use the Subtotal function with an If criteria to sum only the values in column B where column C is equal to Y. Here's how:
Note: The IF function checks each cell in column C to see if it contains the value "Y". If it does, the corresponding value in column B is included in the subtotal. If it doesn't, the value is excluded from the subtotal. The SUBTOTAL function then calculates the sum of the included values.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function with an If criteria
Hi
Use the sumif function: =SUMIF(C2:C6,"Y",B2:B6) Regards, Per "Kathy L." skrev i meddelelsen ... Hello, If the following is example data: A B C 1 Person $ Selected 2 Tom 10000 Y 3 Jack 15000 Y 4 Tom 20000 N 5 Tom 1000 Y 6 Jack 5000 N 7 8 51000 And cell B8 has the Subtotal(9,b2:b6) function, so I can sum based on a filter on column A, is there a way to include a criteria in this subtotal where Column C (selected) is Y? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function with an If criteria
Thank you, however this won't subtotal only the filtered data, filtered in
Column A. I'd like the sum to total based on the Filtered Column A, and only the Y's, however I don't want the user to have to filter on Column C to select the Y's. "Per Jessen" wrote: Hi Use the sumif function: =SUMIF(C2:C6,"Y",B2:B6) Regards, Per "Kathy L." skrev i meddelelsen ... Hello, If the following is example data: A B C 1 Person $ Selected 2 Tom 10000 Y 3 Jack 15000 Y 4 Tom 20000 N 5 Tom 1000 Y 6 Jack 5000 N 7 8 51000 And cell B8 has the Subtotal(9,b2:b6) function, so I can sum based on a filter on column A, is there a way to include a criteria in this subtotal where Column C (selected) is Y? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal function with an If criteria
Found the answer to my question on another thread from Domenic:
To count the number of cells that contain a specific text string: SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),(C2:C6="Y")+0) This seems to do the trick. "Kathy L." wrote: Thank you, however this won't subtotal only the filtered data, filtered in Column A. I'd like the sum to total based on the Filtered Column A, and only the Y's, however I don't want the user to have to filter on Column C to select the Y's. "Per Jessen" wrote: Hi Use the sumif function: =SUMIF(C2:C6,"Y",B2:B6) Regards, Per "Kathy L." skrev i meddelelsen ... Hello, If the following is example data: A B C 1 Person $ Selected 2 Tom 10000 Y 3 Jack 15000 Y 4 Tom 20000 N 5 Tom 1000 Y 6 Jack 5000 N 7 8 51000 And cell B8 has the Subtotal(9,b2:b6) function, so I can sum based on a filter on column A, is there a way to include a criteria in this subtotal where Column C (selected) is Y? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtotal function using criteria | Excel Worksheet Functions | |||
Subtotal with additional criteria | Excel Worksheet Functions | |||
subtotal - multiple criteria | Excel Worksheet Functions | |||
How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
SUBTOTAL and then count with criteria | Excel Worksheet Functions |