|
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
COUNTIF & AND FUNCTIONS
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 |
| All times are GMT +1. The time now is 10:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com