How do I count a text from filtered column please?

[atiq124]

I am using countif(range,"TEXT") to get the total of my TEXT.
Now, I have to filter them by year (2023 or 2024) and countif does not work for filtered column as it still gives me total numbers regardless of the applied filter.
How do I count the number of my text in the filtered column please.

In the attached file, I am looking to count In Progress projects in column B when Column C is filtered for year 2024.

 

[JV0710]

Hi, you can try:

=COUNTIFS(B3:B40,"In Progress",C3:C40,2024)

or

=SUMPRODUCT(--(B3:B40="In Progress")*(C3:C40=2024))

 

[atiq124]

SUMPRODUCT(--(B3:B40="In Progress")*(C3:C40=2024))

Brilliant, both formulas worked, thank you so much for your help. One more question here, if I have 2021 and 2022 years as well in column C; and I want to get the total for only 2023 and 2024 (excluding 2021 and 2022), how do I do that please?

 

[JV0710]

To do a countif for Status "in Progress" and years = (2023 and 2024} you do the following (adjust the range accordingly)

=SUM(COUNTIFS(B3:B40,"In Progress",C3:C40,{2023,2024}))

or

=SUMPRODUCT(--(B3:B40="In Progress")*(C3:C40={2024,2023}))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[atiq124]

To do a countif for Status "in Progress" and years = (2023 and 2024} you do the following (adjust the range accordingly)

=SUM(COUNTIFS(B3:B40,"In Progress",C3:C40,{2023,2024}))

or

=SUMPRODUCT(--(B3:B40="In Progress")*(C3:C40={2024,2023}))

Super, thank you so much for your help

 

[atiq124]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using Microsoft 365 E3.

 

[atiq124]

Another interesting questions for same topic. can we use filters option with dropdown menue on the top of the page to select which years data we want to select, so if we select 2023 and 2024 from there, rest of the data will not appear (2021, 2022)....?

 

[Fluff]

As you have 365 another option is

=COUNT(FILTER(ROW(B3:B100),(B3:B100="in progress")*(BYROW(B3:B100,LAMBDA(br,SUBTOTAL(103,br)))=1)))

This will only count visible rows with In Progress