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?

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:

1. Click on cell B8 where you want to display the subtotal.
2. Type the following formula:
   Code:

   ---

   =SUBTOTAL(9,IF(C2:C6="Y",B2:B6))

   ---

3. Press Ctrl+Shift+Enter to enter the formula as an array formula. This will ensure that the formula calculates correctly.
4. The subtotal will now be displayed in cell B8, and it will only include the values in column B where column C is equal to Y.

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.

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?

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?

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?