Countif multiple criteria in same cell

[CP]

Hi all,

I have a sales worksheet that calculates 'action
codes'using COUNTIF. That is easy. Is there a way to enter
more than one 'action code' in a cell and have the cell
that's doing the calculating count more than one entry?

Ex: *Action Code 1: Opened New Account
*In Column A acion codes are entered.
*There is a summary above using COUNTIF
*COUNTIF(A10:A20="1")is the formula I'm using.

Would it just be simpler to use a second column and do an
arrray? Or is there a way to do this?

Thanks, CP

 

[Paul]

Hi all,

I have a sales worksheet that calculates 'action
codes'using COUNTIF. That is easy. Is there a way to enter
more than one 'action code' in a cell and have the cell
that's doing the calculating count more than one entry?

Ex: *Action Code 1: Opened New Account
*In Column A acion codes are entered.
*There is a summary above using COUNTIF
*COUNTIF(A10:A20="1")is the formula I'm using.

Would it just be simpler to use a second column and do an
arrray? Or is there a way to do this?

Thanks, CP

You can use this (or a similar) array formula:
=SUM(COUNTIF(A10:A20,{1,2,3}))
(An array formula must be entered using CTRL+SHIFT+ENTER rather than just
ENTER.)
This will give the sum of the counts for action codes equal to 1, 2 and 3.
Is this what you want?

 

[Jim]

You do not need to enter quotes around numbers.
=COUNTIF(A10:A20,1)+COUNTIF(A10:A20,2) OR
=COUNTIF(A1:A10,">75")-COUNTIF(A1:A10,">100")

 

[Don Guillett]

I don't understand the question but if you want
Opened new Account =1 then try
="Opened new Account="& countif(a10:a20,1)