#### Countif

```I am trying to solve the following problem

i) cells A1 to A10 have either M or F (male or female)
ii) cells B1 to B10 have either (grades) A B or C
iii) I want (eg cell B13) to state the number of female students who scored
A... and cell B14; how many female students who gained B etc

I have tried various things including countif, sumif etc. It seems like a
straight forward problem but the solution evades me!

Eddie

```
 0
vareye (1)
8/21/2003 12:18:47 AM
excel 39879 articles. 2 followers.

3 Replies
777 Views

Similar Articles

[PageSpeed] 31

```=SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1)))

in Cell B13 and filled down to Cell B15.

Alan Beban

Eddie wrote:
> I am trying to solve the following problem
>
> i) cells A1 to A10 have either M or F (male or female)
> ii) cells B1 to B10 have either (grades) A B or C
> iii) I want (eg cell B13) to state the number of female students who scored
> A... and cell B14; how many female students who gained B etc
>
>
> I have tried various things including countif, sumif etc. It seems like a
> straight forward problem but the solution evades me!
>
>
> Eddie
>
>

```
 0
beban (93)
8/21/2003 12:36:53 AM
```Or to use slightly less esoteric methods to get the values "A", "B", and "C"
to compare against......

1. Using SUMPRODUCT

Define named range Sex as A1:A10
Define named range Grade as B1:B10

To count the number of  Male students with a grade of A, enter the
formula  =SUMPRODUCT((Sex="M")*(Grade="A")) in the desired cell
To count the number of Female students with grade of B, enter the
formula  =SUMPRODUCT((Sex="F")*(Grade="B")) in the desired cell
etc.

----------------------------------------------------------------------------
--------
2. Alternately, count ones and zeros in SUM with a nested IF, entered as
array formulas.

Define named range Sex as A1:A10
Define named range Grade as B1:B10

To count the number of  Male students with a grade of A, enter the
formula  =SUM(IF(Sex="M",IF(Grade="A",1,0))) in the desired cell
To count the number of Female students with grade of B, enter the
formula  =SUM(IF(Sex="F",IF(Grade="B",1,0))) in the desired cell
etc.

Don't forget to use <CTRL><SHIFT><ENTER> to enter the formula as an
array formula (Excel will put little braces around the formula to confirm
it's an array formula)
----------------------------------------------------------------------------
-----------------------------
Variations:

a. Put descriptive labels next to the formulas so you'll know what the
number means. For example the result "4" by itself is not particularly
informative unless you can tell at a glance that's the number of Male
students with a grade of "B". More flexible is to use cell values next to
the final result as the value to compare against -- for example rather than
typing in Sex="M", place the value "M" in cell D14 and use Sex=\$D14. You can
then change the results at will by changing the value of a cell rather than
hard coding it in the formula. You can also use those values to build a
string for the label of the final value(s) to make the approach even more
general.

b. You can also add checksums in selected cells to flag some missing
values. For example, if you've entered the named ranges as defined above, a
cell with the the formula:

can be used to compare against totals of your sub-categories. In your
example, that formula will give the value of 10. If you then sum the
sub-category reulsts (Males with A + Males with B + Males with C + Females
with A...etc.) and add a text messaage if it doesn't add to 10 -- tells you
one of your two columns has a missing value. Further refinements would use
other cells with counts of F and M values in column A, and counts of A,B,
and C values in column B. That will allow you to more specifically flag
missing or incorrect values (if someone inadvertently put a "Q" instead of M
or F, or entered a grade of "Z"). Not that important if you're dealing with
only 10 sets of values, since you can see all the data at once, but very
useful if you're dealing with more data than you can see on a single screen.

Regards,

Sox

"Eddie" <vareye@maricourt.net> wrote in message
news:EdU0b.1312\$rb1.204@news-binary.blueyonder.co.uk...
> I am trying to solve the following problem
>
> i) cells A1 to A10 have either M or F (male or female)
> ii) cells B1 to B10 have either (grades) A B or C
> iii) I want (eg cell B13) to state the number of female students who
scored
> A... and cell B14; how many female students who gained B etc
>
>
> I have tried various things including countif, sumif etc. It seems like a
> straight forward problem but the solution evades me!
>
>
> Eddie
>
>

```
 0
SoxF14 (3)
8/21/2003 2:29:18 AM
```Tom Lorenzo wrote:
> Or to use slightly less esoteric methods to get the values "A", "B",
>and "C" . . .

Yes, well, the point was to have a single formula to be filled down. At
least I didn't (before your comment) suggest

which is a single formula (to get the values "F", "M", "A", "B" AND "C")
to be filled down and across to provide the results for both Female and
Male.

Alan Beban

```
 0
beban (93)
8/21/2003 4:40:19 AM

Similar Artilces:

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 \$0.00 2 08/13/2006 1 \$10.00 3 08/14/2006 1 \$20.00 4 08/01/2006 0 \$0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1\$3371a4f0\$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Countif() formula
Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:\$D\$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...

CountIF by Background Color
Need a formula to count the cells in a range with a background color yellow (6). You can use this short UDF called CountYellow. Note that it only counts true background color, not format generated by conditional formatting. Detecting conditional formatting is quite a bit more complicated... Public Function CountYellow(r As Range) As Double Application.Volatile = True CountColor = 0 For Each c In r If c.Interior.ColorIndex = 6 Then CountColor = CountColor + 1 End If Next End Function -- Best Regards, Luke M *Remember to click "yes" if this post helped you...

Countif Formula with changing criteria
Hi. I'm trying to create a formula that will count how many numbers in a list of ten that are greater than a changing number in a cell. The cell changes as it has a formula attached to it. I used as the criteria >b1, but for some reason this doesn't work. As this number changes it has to be greater than whatever the cell value is at that time. -- Thanks! Stephen try =sumproduct(--(range>B1),1) "Stephen" wrote: > Hi. I'm trying to create a formula that will count how many numbers in a list > of ten that are greater than a changing number in a cell. T...

countif
I am trying to count a range of numbers in a column I created in excel to see how many times a particlar range appears. For example, I have a column with 10 rows that has a unique zip codes in each of the cells. 90001 90003 91005 90009 90207 94001 96007 97009 98009 98991 I want to know how many times each number below 94000 occurs and how many instances in which numbers greater than 94000 occur. What formula do I use. Thank you. cp Data in A1 to A10. Try this: =COUNTIF(A1:A10,">94000")&" above 94000 and "&COUNTIF(A1:A10,"<94000")&&quo...

IF COUNTIF
I am trying to get this to work =IF(COUNTIF(D83:S83,"=X"),"N","Y"),+IF(COUNTIF(L83,"=X"),"N","P") so that if L83 is selected the result in the cell would be P. Perhaps I am using the wrong function. Ideas anyone? Thank you. -- Mary Looks like the wrong function. When you say selected, what do you mean? It looks like you might just want =IF(L83="X","P","N") HTH Bob "Hmmmmm?" <Hmmmmm@discussions.microsoft.com> wrote in message news:DE1EDCE6-5F48-455A-B50E-185...

countif blanks
I need to count the number of blanks in a range. I'm trying countif(H1:H20,"<>""") but it's not working. Any ideas? =COUNTIF(H1:H20,"<>"&"") Or to account for the "dreaded SPACE BAR" =SUMPRODUCT((LEN(TRIM(H1:H20))>0)*1) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Radon" <Radon@discussions.microsoft.com> wrote in message news:0924636B-562F-4530-B972-FB7964F5F58C@microsoft.com... >I need to count the number of blanks in a range. I'm trying > countif(H1:...

Need help with CountIF function
Hello, Please help. I have a columne A with department codes A, B, C, ... the department codes are multiple lines, column B with numbers when there are activity in that department. Can I do a countif function or other function to summerize how many activity each department? To count how many times there is activity for dept A: =SUMPRODUCT(--(A2:A100="A"),--(ISNUMBER(B2:B100)) To sum the numbers in column B associated with dept A: =SUMIF(A:A,"A",B:B) -- Best Regards, Luke M "Flo" <Flo@discussions.microsoft.com> wrote in message...

COUNTIF Range
Does the COUNTIF range have to be a continuous range (i.e. A1:E1) or could it be a non-continuous range of cells (i.e. A1,D1,G1,J1,M1)? I want to count the number of occurances of a cell value being > 75 in the non-continuous range of A1,D1,G1,J1,M1. The COUNTIF function does not like the non-continuous range. Is there a better way of doing this. It is not an option to move the columns of data so they are a continuous range. Mike -- mluetkem ------------------------------------------------------------------------ mluetkem's Profile: http://www.excelforum.com/member.php?action=...

COUNTIF with or + and variables
I'm having a hard time figuring this one out My challenge is to count if column [I] is "A" or "B" and column [K] and [O] are "C" I'm trying to count all the ones in column [J] that meet the specific conditions. I tried an array but it didn't seem to take. Any hints are greatly appreciated. Try this… =COUNTIF(I:I,"A")+COUNTIF(I:I,"B")+COUNTIF(K:K,"C")+COUNTIF(O:O,"C") Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Dances...

Countif
I have 2 columns A & B with the following values: A B 1 4 1 2 5 2 3 6 3 How can I count the number of cells that A=3 with B="" using excel built-in functions? How can I combine the 2 criteria? Thanks A Lot!! Frank =sumproduct((A1:A10=3)*(B1:B10="")) "frank716" <frank716.t8ewn@excelforum.com> wrote in message news:frank716.t8ewn@excelforum.com... > I have 2 columns A & B with the following values: > > A B > 1 4 > 1 > 2 5 > 2 > 3 6 > 3 > > How can I count the number of cells that A=3 with B="&...

COUNTIFS with OR
Hi I have a sheet as below which is around 1000 rows long. Column A contains upto 100 different site codes. Column B always has 2 letters and there are 40 different combinations of the 2 letters possible. I am trying the following (which does not work obviously): COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc)) A B 1 1223 Red 2 1223 Blue 3 1234 Blue 4 1223 Green 5 1235 Orange I know I could use COUNTIF+COUNTIF+.... multiple times but I would need 20 added together and want to simplify the formula. I am now losing the will to live looking for a...

Countif?
I'm looking for some help with the following; What I want to do in another cell is look up a range on column A and if its "text 1" count the number of "text 2" in column B. ie A1 B1 Text 1 Y Text 1 N Text 2 Y Text 2 Y This would result in For text 1 with Y = 1 For text 1 with N = 1 For text 2 with Y = 1 For text 2 with N = 0 Any help would be appreciated. Thanks, Teeb -- teeb ------------------------------------------------------------------------ teeb's Profile: http://www.excelforum.com/member.php?action=getinfo&...

SUMPRODUCT or COUNTIF??
All, I have been working on this for ages and can't get it right, and yet i feel it should be really simple! Can anyone help? I have an excel workbook which has 2 worksheets. On worksheet 1 (called 2005Stats) is a table: ColumnA ColumnB 01/01/2005 Placed 10/01/2005 Placed 15/01/2005 Open 16/02/2005 Closed 23/06/2005 Placed ColumnA has over 100 records with all different dates of this year, ColumnB has either "Placed", "Closed" or "Open" On worksheet 2, called 2005Calcs i want to have...

Countifs
Hi All, New in Excel 2007. Why is Countifs giving an error when I try to enter it with combined formulas like: =countifs(month(range);1;other_range;1) With kind regards, JP >=countifs(month(range);1;other_range;1) SUMIF SUMIFS COUNTIF COUNTIFS AVERAGEIF AVERAGEIFS These functions can only handle "straight" comparisons. That is, you can't manipulate a range array to test for a condition. In the formula above you're trying to manipulate the range array by first testing for the month. MONTH(range) = 1 The test has to be a "...

COUNTIF question
Hey all, i'm stuck. I need to do a formula that will count the number of cells in column 'A' only if the corresponding cell in column 'B' has a value of '0'. =COUNTIF(O:0,"0")+COUNTIF(P:P,">0") - just counts both, and that's as far as i have got. Can anyone help me please. Thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ This is probably a cheats way of doing it but... I would create a third column with...

Countif(and
I want to count cells in Column C when column A criteria are met and Column B criteria are met. ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) =SUMPRODUCT(--(A1:A5=M3),--(B1:B5=M4),--(C1:C5<>"")) -- David Biddulph "John" <John@discussions.microsoft.com> wrote in message news:EE92CF58-B41A-49F3-BE6E-00567F3DF568@microsoft.com... >I want to count cells in Column C when column A criteria are met and Column >B > criteria are met. > ex. Countif(AND(A1:A5,M3,B1:B5,M4,COUNT(C1:C5) John, It's not entirely clear what you mean ...

COUNTIF formula?
Hi, I=92m using Excel 2002 and I have a question about using a formula to determine if certain words are found within a cell. For example, I want to determine if several words are found in each cell in column A and, if so, I want to put the word =93Yes=94 in the corresponding cell in column B. If the word isn=92t found then I just want that cell in column B to remain blank. So, in the following example, I want to see if the following words: Oranges Apples Lemons are found in any of the cells in column A. The result would look like this: Column A Column B Apples, Oranges, Pears Yes ...

Sorting and COUNTIF
This may be a double post; apologies. This problem seems to happen only when my RANGE is on a different worksheet than my CRITERIA. When I run a COUNTIF, if I sort the table, the criteria returns the count of whatever is in the position of where the CURRENT criteria USED to be. I don't want that. I never know how to explain relative and absolute references, so let me give you an example. Sheet 2 has an inventory of four items: Hard-boiled eggs, oil filters, Jonas Brothers posters, and dry-erase markers. For simplicity, let's call it "Sheet 2! Range". I...