ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   COUNTIF Function (https://www.excelbanter.com/excel-worksheet-functions/164439-countif-function.html)

mpenkala

COUNTIF Function
 
Hi, I'm having a few problems with the COUNTIF function. This is what I'm
looking for.

Range is A40:A70. This contains numbers between 0 and 21.

Then I have columns B,C,D
In columnB I would like to have COUNTIF(A40:A70, odd number) (So count all
odd number's in the range except for 0)
In columnC I would like to have COUNTIF(A40:A70, 12 AND <21) (So count all
numbers between 12 and 21)
In columnD I would like to have COUNTIF(A40:A70, 1,4,7,13,19) (So count all
numbers I've requested)

Hope this makes sense. I'm sure it's something simple, but I can't figure
it out.
Thanks again!
Matt


Don Guillett

COUNTIF Function
 
try this idea
=sumproduct((a2:a22=0)*(a2:a22<21))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mpenkala" wrote in message
...
Hi, I'm having a few problems with the COUNTIF function. This is what I'm
looking for.

Range is A40:A70. This contains numbers between 0 and 21.

Then I have columns B,C,D
In columnB I would like to have COUNTIF(A40:A70, odd number) (So count all
odd number's in the range except for 0)
In columnC I would like to have COUNTIF(A40:A70, 12 AND <21) (So count
all
numbers between 12 and 21)
In columnD I would like to have COUNTIF(A40:A70, 1,4,7,13,19) (So count
all
numbers I've requested)

Hope this makes sense. I'm sure it's something simple, but I can't figure
it out.
Thanks again!
Matt



JE McGimpsey

COUNTIF Function
 
One way:

B: =SUMPRODUCT(--(MOD(A40:A70,2)=1))

C: =COUNTIF(A40:A70, "12") - COUNTIF(A40:A70,"=21")

D: =SUM(COUNTIF(A40:A70,{1,4,7,13,19}))

(So count all odd number's in the range except for 0)


Um... 0 isn't odd...

In article ,
mpenkala wrote:

Hi, I'm having a few problems with the COUNTIF function. This is what I'm
looking for.

Range is A40:A70. This contains numbers between 0 and 21.

Then I have columns B,C,D
In columnB I would like to have COUNTIF(A40:A70, odd number) (So count all
odd number's in the range except for 0)
In columnC I would like to have COUNTIF(A40:A70, 12 AND <21) (So count all
numbers between 12 and 21)
In columnD I would like to have COUNTIF(A40:A70, 1,4,7,13,19) (So count all
numbers I've requested)

Hope this makes sense. I'm sure it's something simple, but I can't figure
it out.
Thanks again!
Matt


JE McGimpsey

COUNTIF Function
 
????

In article ,
"Don Guillett" wrote:

try this idea
=sumproduct((a2:a22=0)*(a2:a22<21))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mpenkala" wrote in message
...
Hi, I'm having a few problems with the COUNTIF function. This is what I'm
looking for.

Range is A40:A70. This contains numbers between 0 and 21.

Then I have columns B,C,D
In columnB I would like to have COUNTIF(A40:A70, odd number) (So count all
odd number's in the range except for 0)
In columnC I would like to have COUNTIF(A40:A70, 12 AND <21) (So count
all
numbers between 12 and 21)
In columnD I would like to have COUNTIF(A40:A70, 1,4,7,13,19) (So count
all
numbers I've requested)


mpenkala

COUNTIF Function
 
Hi JE,

Thanks for the help - everything works great. As for the 0 not being odd...
sorry, my bad. I just asked for odd, but I was looking for odd and even
(hence he asking about 0). But I found the answer to that on another post.

Thanks again, this site rocks!
Matt


"JE McGimpsey" wrote:

One way:

B: =SUMPRODUCT(--(MOD(A40:A70,2)=1))

C: =COUNTIF(A40:A70, "12") - COUNTIF(A40:A70,"=21")

D: =SUM(COUNTIF(A40:A70,{1,4,7,13,19}))

(So count all odd number's in the range except for 0)


Um... 0 isn't odd...

In article ,
mpenkala wrote:

Hi, I'm having a few problems with the COUNTIF function. This is what I'm
looking for.

Range is A40:A70. This contains numbers between 0 and 21.

Then I have columns B,C,D
In columnB I would like to have COUNTIF(A40:A70, odd number) (So count all
odd number's in the range except for 0)
In columnC I would like to have COUNTIF(A40:A70, 12 AND <21) (So count all
numbers between 12 and 21)
In columnD I would like to have COUNTIF(A40:A70, 1,4,7,13,19) (So count all
numbers I've requested)

Hope this makes sense. I'm sure it's something simple, but I can't figure
it out.
Thanks again!
Matt




All times are GMT +1. The time now is 05:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com