COUNTIF issue

[mattbird]

I have a table that I want to pull information per quarter ie Apr-Jun, Jul-Aug etc.
In cells A3:A20 I have used Q1, Q2, Q3, Q4 for the quarters.
In cell B3 I have created a list ‘All, Q1, Q2, Q3, Q4’, so I can select which quarter I want.
In cell B5 I have use the following formula to count:
=COUNTIF(A3:A20,B3) which works and provides me with one of the selected text from the list in B3, ie Q2. However, how do I get the formula to count all text A3:A20, if I select ‘All’ from the list at B3?

Can this be done?

Any help or advice is greatly appreciated.

Matt

 

[awoohaw]

try this:

=IF(B3="ALL",COUNT(A3:A20),COUNTIF(A3:A20,B3))

 

[Alex Blakenburg]

to count all text A3:A20

In @awoohaw's formula, you will need to swap the Count for CountA to count "text"

 

[Fluff]

Another option

=COUNTIFS(A:A,IF(B3="All","?*",B3))

 

[mattbird]

try this:

=IF(B3="ALL",COUNT(A3:A20),COUNTIF(A3:A20,B3))

Thanks for your help. It counts all the Q1,2,3,4 but ‘All’ returns a 0?

 

[mattbird]

Another option

=COUNTIFS(A:A,IF(B3="All","?*",B3))

Thanks for your help. This works. If B3 is empty it returns a 0, how do I get it to show as blank, if nothing is selected from the list? Would it need an IFERROR?

 

[Alex Blakenburg]

Thanks for your help. It counts all the Q1,2,3,4 but ‘All’ returns a 0?

I take it you didn't make the suggested modification to awoohaw's submission.
This also adds the blank criteria.
You can do the same think to Fluff's option.

        =IF(B3="","",IF(B3="ALL",COUNTA(A3:A20),COUNTIF(A3:A20,B3)))