How to use the SUBTOTAL function with the COUNTIF function

Question

Hello, please show me how to use the SUBTOTAL function with the COUNTIF function....

Let's say I filter columns B-E to show only the dates from 2022.

What formula can I use to display how many times the letter "S" occurs in columns F-G in the filtered list only (not from all the dates)

What formula can I use to display how many times the letter "L" occurs in columns H-I in the filtered list only (not from all the dates)

What formula can I use to display the sum of the numbers in columns L-M in the filtered list only (not from all the dates)

Thanks for your help

Matthew Miller

Answer 1

You have two options:

Add a column of formulas that will return 1s and 0s based on the filtering:

=SUBTOTAL(3,B14)

Copied down to match your data.

Then use

=COUNTIFS(column of new formulas, 1, Old COUNTIF arguments)

Or just change your COUNTIF to COUNTIFS with two extra date comparisons

">="& DATE(Yearcell,1,1)

"<"& DATE(Yearcell+1,1,1)

Answer 2

Hi,

I'm Sneha and I'd be happy to help you out with your question.

1. To use the SUBTOTAL function with the COUNTIF function, you can use the following formula:

To count the number of times the letter "S" occurs in columns F-G in the filtered list only:

=SUBTOTAL(3,F2:G2)

Note: The 3 in the formula indicates that you want to use the COUNTIF function for the range. The range in this case is F2:G2, which is the filtered list.

2. To count the number of times the letter "L" occurs in columns H-I in the filtered list only:

=SUBTOTAL(3,H2:I2)

3. To sum the numbers in columns L-M in the filtered list only:

=SUBTOTAL(9,L2:M2)

Note: The 9 in the formula indicates that you want to use the SUM function for the range. The range in this case is L2:M2, which is the filtered list.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha

Answer 3

Thanks Miss Sneha Gupta,

How do we include in this formula, =SUBTOTAL(3,F2:G2), that it is to search for the letter S?

Thanks,

Matthew Miller

Answer 4

You'd be far better off doing what Bernie suggested. Otherwise you'll need a combination of SUMPRODUCT and OFFSET with your SUBTOTAL function. Or, if you have the new functions, you can probably use BYROW and LAMBDA.

Answer 5

Hi,

I'm Sneha and I'd be happy to help you out with your question.

To include in the formula =SUBTOTAL(3,F2:G2) that it is to search for the letter 'S', you can use the SEARCH function in combination with the SUBTOTAL function. The SEARCH function can be used to find the position of a specified text string within a larger text string.

Here's an example of how you can use the SEARCH function in combination with the SUBTOTAL function to search for the letter 'S':

=SUBTOTAL(3, SEARCH("S", F2:G2))

This formula will return the sum of all values in the range F2:G2 that contain the letter 'S'. If a value in the range does not contain the letter 'S', it will be ignored by the SUBTOTAL function.

Note that the SEARCH function is case-sensitive, so it will only return results for the capital letter 'S'. If you want to search for both the capital and lowercase letter 's', you can use the LOWER function to convert all text to lowercase before searching. For example:

=SUBTOTAL(3, SEARCH("S", LOWER(F2:G2)))

This formula will return the sum of all values in the range F2:G2 that contain either the capital or lowercase letter 's'.

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help to the best of my ability.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Sneha