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

The standard way to do that kind of thing is
=SUMPRODUCT(--(A1:A5=6401),--(B1:B5="Mobile"))

more info here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Allllen


"mike_vr" wrote:

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

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
--
Brevity is the soul of wit.


"mike_vr" wrote:

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

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.

"mike_vr" wrote:

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

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

Cheers,

Mike

"Allllen" wrote:

The standard way to do that kind of thing is
=SUMPRODUCT(--(A1:A5=6401),--(B1:B5="Mobile"))

more info here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Allllen


"mike_vr" wrote:

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

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

"Craig Covey" wrote:

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.

"mike_vr" wrote:

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

I believe that COUNTIFS won't appear until Excel 2007.
--
David Biddulph

"Craig Covey" <Craig wrote in message
...
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.

"mike_vr" wrote:

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

http://www.xldynamic.com/source/xld....T.html#explain
--
Allllen


"mike_vr" wrote:

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

Cheers,

Mike

"Allllen" wrote:

The standard way to do that kind of thing is
=SUMPRODUCT(--(A1:A5=6401),--(B1:B5="Mobile"))

more info here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Allllen


"mike_vr" wrote:

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