COUNTIF & AND FUNCTIONS

M

mike_vr

Hi all,

I'm having a problem with the structuring of these two functions into one.

I have two columns and I'm trying to get a count where they both agree to
certain parameters.

Column A - Numerical
6400
6401
6402
etc

Column B - Text
Blackberry
Mobile
etc

So what I'm trying to do is get a count where A = 6400 AND B=Mobile for
example.

I have this at the moment, but not getting any luck!!!

=countif(and((a2:a209)="6400",(b2:b209)="Mobile"))

Any help would be appreciated!!!

Thanks,

Mike
 
D

Dave F

I would create a helper column which concatenates the two cells and then
count the concatenated strings.

Assume A2 = 6400 and B2 = Mobile.

Then in C2 =CONCATENATE(A2,B2) yields 6400Mobile

=COUNTIF(C2:C209,"6400Mobile") will get you what you want.

Dave
 
C

Craig Covey

First, you should change the function. Don't use COUNTIF but COUNTIFS
(multiple ranges and criterion allowed).

Here's the format:

=COUNTIFS(A2:A209,"6400",B2:B209,"Mobile")

That way, you won't need to create any concatenated column, etc.
 
M

mike_vr

Thanks Allllen, that's worked out nicely. Just by the way, what is the
significance of th (--( before the ranges?

Cheers,

Mike
 
M

mike_vr

Thanks Craig,

Unfortunately this backwards company hasn't reached the world of Excel 12
yet. Would there be anyway of doing this sort of thing in Excel... wait for
it.... 97!!!

Cheers,

Mike
 
Top