excel counting items 1

[boiker]

i know it would probably be easier to create a database but.......

EX.

SEX GRADE
M A
M B
M A
F C
F A
M D

i need a statement that will give me a total number of boys with a's
i've tried countif() but can't figure out a way to make it check two columns.

 

[Hasit]

Use arrays. You can then use Countif, and include multiple columns and rows.

For information on how to set it up an array, have a look at: faq68-660

If you need specific help, let me know.

 

[boiker]

i'm really having trouble understanding all this array stuff

i want the excel sheet to count all the values in B2:b10 that are "m" AND c2:c10 are "a" and report 1 number to me. So that i can say, this many boys had A's

is there an example i could look a?

 

[Hasit]

OK. Type in the following formula in cell C1 (assuming your table above starts in cell A1):

=SUM(IF($A$1:$A$6="M",IF($B$1:$B$6="A",1,0),0))

and then hit CTRL-SHIFT-ENTER at the same time.

You should then get:

{=SUM(IF($A$1:$A$6="M",IF($B$1:$B$6="A",1,0),0))}

This will check the column A1 through A6 for instances of "M" and then total up all instances of grade "A" that correspond with the number of "M"s.

All you then need to do is copy this formula (remembering to hit CRTL-SHIFT-ENTER to get the curly brackets (you can't just type then in)) for each instance you are checking for, i.e. grade B, C and D and the same formulas, but to check for females "F".

 

[boiker]

thank you, i was looking it at all wrong. i understand how the array is working now.

 

[boiker]

one more question... why the 1,0),0)) stuff at the end of the array?

 

[Hasit]

Its part of the IF function. The structure of an IF function is IF(condition,then "THIS",else "THAT&quot

 

[boiker]

To continue the question. fields are:

Case Number Result
ZCA MV 1212 Approved
ZCA 2321 Approved
ZCA MV 7584 Denied
ZCA MV 1222 Approved
ZCA 6433 Under Review

that statement from above won't reconize wild cards. how can i have it tell me all the cases that are ZCA MV* and approved?

 

[Hasit]

Thats true, you cannot pass wildcards in Excel. I am sure there is a more elegant way to achieve what you want, but if you are only searching for 6 characters (ZCA<SPACE>MV = 6 chars), AND all the case numbers in your example start with ZCA, then you could use the following:

{=SUM(IF(LEFT($A$2:$A$6, 6)=&quot;zca mv&quot;,IF($B$2:$B$6=&quot;Approved&quot;,1,0),0))}

In the above formula, I am testing the first 6 characters, that are left most in the column A to see if they equal &quot;zca<space>mv&quot;. If they do, then I test the corresponding cell entry in column B. If it equals &quot;approved&quot;, then I sum it.

IN your example, the answer comes out as 2, so it works!

Incidentally, the formula above is an array formula again, so be aware of the CRTL-SHIFT-ENTER process.