Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
please help how to combine IF function with Countif function | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
Embed a countif function in subtotal function? | Excel Worksheet Functions | |||
countif function | Excel Worksheet Functions | |||
COUNTIF Function | Excel Discussion (Misc queries) |