CAn you perform a countif formula with 2 conditions? 1

[wec43wec]

Can you perform a "countif" statement by using 2 conditions?

I tried using the same format as I would use in an "if statement" but I bget an error.

My code that I used is as follows

=counmtif(A1:a9,"20001") gives me 2

Now I want to include another condition of the zip code 20003.

=countif(and(A1:A9,"20001","20003") should give me 5


A

1. 20001
2. 20003
3. 20004
4. 20005
5. 20003
6. 20001
7. 20003
8. 20005
9. 20004

 

[bluedragon2]

=COUNTIF(A1:A9,"20001") + COUNTIF(A1:A9,"20003)


[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SPV]

Blue is spot on with the solution for your scenario. But it only works because the values you are testing are mutually exclusive (each cell cannot be both 20001 and 20003 simultaneously).


With COUNTIF the first parameter is the range to check and the second parameter is the criterion (note, this is singular). So countif(and(A1:A9,"20001","20003")) won't work because you have only given it one parameter.

The criterion can be a simple value (e.g. 20001 or "20001") or a comparison (e.g. > 20005) but it doesn't let you get too clever by adding more complex logic.

For more complex scenarios you can add a new column which performs the necessary tests. Then use COUNTIF on that new column.

For example, you may a list of names and you want to count which ones are exactly 5 characters long (I didn't say the example had to be realistic).

So if column A has:
[tt]Alice
Ben
Carole
Darren
Edgar[/tt]
you put [tt]=LEN(A1)[/tt] etc into B1 etc.

The you perform the count through [tt]=COUNTIF(B1:B9,5)[/tt] (giving the answer 2).

I hope that helps. If not, I have just gone off on a Sunday night ramble?

 

[wec43wec]

The solution given by BLUEDRAGON2 did the trick.

Star for BLUEDRAGON2 - Thanks !

SPV - as always, excellent response and thanks for providing a different view to the solution of my problem.

You get a STAR also !

 

[SPV]

Thank you wec. But pity the students if I ever change role from dba to instructor... ;-)

 

[xlbo]

SPV - no real need for a helper column - SUMPRODUCT can be used for multi / difficult criteria:

=SUMPRODUCT((LEN(B1:B9)=5)*(1))

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

or more specific to wec43wec's problem:

=SUMPRODUCT(--(A1:A9="20001")+--(A1:A9="20003"))

Note: * for AND, + for OR, -- to coerce to integer

Cheers,

Roel

 

[yogia]

Here is another one ...

=SUM(COUNTIF(A:A,{20001,20003}))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[ALRice]

I have tried multiple solutions, including sumproduct, if, and sum, count if, yet still getting errors, or wrong answers.

On one sheet I have a table with 4 columns,
Date Area Records Sum
6/25/2007 2 12 56
6/25/2007 3 10 45
6/30/2007 5 28 150
6/30/2007 2 32 175
7/1/2007 4 10 36
7/1/2007 1 24 119
7/1/2007 3 26 128
7/1/2007 2 27 137
7/1/2007 2 29 146
7/1/2007 4 31 155
7/7/2007 1 33 164
7/7/2007 6 35 173

On another sheet, I need to count the number of records that happenened on a specific date in a specific area.

I can get the number of total records for all dates for each area: =COUNTIF(Data!B2:B2000,B3)
Or all areas for each date: =COUNTIF(Data!A2:A227,A2)
Using =COUNTIF(data!A:A,A2)-COUNTIF(data!B:B,B3) will get me the total records on the date for all other areas except area called out in B3, but using =COUNTIF(data!A:A,A2)-COUNTIF(data!B:B,"<>"&B3) will get me a negative number


But when I try to filter it by the date AND area:=SUMPRODUCT(data!A2:A73=A2)*(Data!B2:B73=B3)
a2 being date and b3 being area - I get a #VALUE! error =SUMPRODUCT(Data!$A$2:$A$65=$A$2,Data!$B$2:$B$65=B3)
will bring me a 0
in trying =SUM(COUNTIF(data$A:$A,$A2)*(data!$B:$B=$B3))
or =COUNTIF(data!B2:B2000,B5*COUNTIF(data!A5:A2000,B5))
both return 0, though I know area 2 has 56 records on 7/1 and area 4 has 41 records on 7/1


Any ideas on what I am doing wrong? or is there a much much simpler way to do this?

Thank you for everything you do.






Aim for the moon, for even if we miss, we are still among the stars.

 

[SkipVought]

ALuce,

Please post your question in a NEW THREAD. Thanks.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[ALRice]

Nevermind, again

Aim for the moon, for even if we miss, we are still among the stars.

 

[wec43wec]

Yogia

Your formula as indicated above:

=SUM(COUNTIF(A:A,{20001,20003}))

Question - how does the "{" work?

I have never used this in any of my formulas.

 

[GlennUK]

wec43wec

the {} indicate the start and end of array constants. Another example of them at work is here:

=SUMPRODUCT(--(A1:A65535={20001,20003}))

Help says:

Items that an array constant can contain

· Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such as #N/A.
· Numbers in array constants can be in integer, decimal, or scientific format.
· Text must be enclosed in double quotation marks, for example "Tuesday".
· You can use different types of values in the same array constant, for example, {1,3,4;TRUE,FALSE,TRUE}.
· The values in an array constant must be constants, not formulas.

· Array constants cannot contain $ (dollar signs), parentheses, or % (percent signs).
· Array constants cannot contain cell references.
· Array constants cannot contain columns or rows of unequal length.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.