Home |
Search |
Today's Posts |
#1
|
|||
|
|||
countif OR function
Hi
I am using the following formula to calculate the number of males and females in my class lists. It works well. COUNTIF(D3:D28,"M") COUNTIF(D3:D28,"F") I want to count the total number of Males and Females, I am currently using a a sum function on the range but is it possible to use an OR expression inside a function? Basically what I was looking for was a version of what follows (that works!) COUNTIF(D3:D28,"M" or "F") so if there were 5 males and 7 females it would return the value 12. this is more for my own education than anything else. Thanks |
#2
|
|||
|
|||
Hi!
Try one of these: =COUNTIF(D3:D28,"M")+COUNTIF(D3:D28,"F") =SUMPRODUCT(--(D3:D28="M")+(D3:D28="F")) Biff "nospam" wrote in message ... Hi I am using the following formula to calculate the number of males and females in my class lists. It works well. COUNTIF(D3:D28,"M") COUNTIF(D3:D28,"F") I want to count the total number of Males and Females, I am currently using a a sum function on the range but is it possible to use an OR expression inside a function? Basically what I was looking for was a version of what follows (that works!) COUNTIF(D3:D28,"M" or "F") so if there were 5 males and 7 females it would return the value 12. this is more for my own education than anything else. Thanks |
#3
|
|||
|
|||
You could use:
=SUM(COUNTIF(D3:D28,{"M","F"})) or just =COUNTIF(D3:D28,"M") + COUNTIF(D3:D28,"F") (in one cell) nospam wrote: Hi I am using the following formula to calculate the number of males and females in my class lists. It works well. COUNTIF(D3:D28,"M") COUNTIF(D3:D28,"F") I want to count the total number of Males and Females, I am currently using a a sum function on the range but is it possible to use an OR expression inside a function? Basically what I was looking for was a version of what follows (that works!) COUNTIF(D3:D28,"M" or "F") so if there were 5 males and 7 females it would return the value 12. this is more for my own education than anything else. Thanks -- Dave Peterson |
#4
|
|||
|
|||
=SUMPRODUCT((D3:D28="M")+(D3:D28="F"))
-- Regards, Dave "nospam" wrote: Hi I am using the following formula to calculate the number of males and females in my class lists. It works well. COUNTIF(D3:D28,"M") COUNTIF(D3:D28,"F") I want to count the total number of Males and Females, I am currently using a a sum function on the range but is it possible to use an OR expression inside a function? Basically what I was looking for was a version of what follows (that works!) COUNTIF(D3:D28,"M" or "F") so if there were 5 males and 7 females it would return the value 12. this is more for my own education than anything else. Thanks |
#5
|
|||
|
|||
Thanks everyone
that's great. Dave Peterson writes You could use: =SUM(COUNTIF(D3:D28,{"M","F"})) or just =COUNTIF(D3:D28,"M") + COUNTIF(D3:D28,"F") (in one cell) nospam wrote: Hi I am using the following formula to calculate the number of males and females in my class lists. It works well. COUNTIF(D3:D28,"M") COUNTIF(D3:D28,"F") I want to count the total number of Males and Females, I am currently using a a sum function on the range but is it possible to use an OR expression inside a function? Basically what I was looking for was a version of what follows (that works!) COUNTIF(D3:D28,"M" or "F") so if there were 5 males and 7 females it would return the value 12. this is more for my own education than anything else. Thanks |
#6
|
|||
|
|||
Biff wrote:
.... =SUMPRODUCT(--(D3:D28="M")+(D3:D28="F")) -- is redundant, because the addition coerces both logical arrays to numbers. Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
COUNTIF and then SUM in Same Function | Excel Worksheet Functions | |||
A COUNTIF function Challenge | Excel Worksheet Functions | |||
hOW TO USE THE AND OPERATOR WITH THE COUNTIF FUNCTION | Excel Worksheet Functions |