# Look at a row, determine if a field has a certain value, then count the number of specific values in a different field

This is a rather complex question to explain, I fear!

Here's the sample data I'm working with:

What I'm trying to do is this:

Count the number of Gold Gifted students (i.e. where column G has a value of Y) that ALSO have a KS4 TARGET (column I) value of A or A*.

So, for instance, if the list has 5 Gold Gifted students, but only 4 of those students have a target grade of A* or A, the value I would want displayed is 4.

I'm not even sure where to start. I've tried the following formula but I'm not sure I'm even on the right lines:

=SUM(('Student Breakdown'!\$G\$10:\$G\$272="Y")*('Student Breakdown'!\$I\$10:\$I\$272="A*")*('Student Breakdown'!\$I\$10:\$I\$272="A"))

Can anyone help? Thanks in advance,

-

Try the COUNTIFS function.

With your data layout, I think you would use

=COUNTIFS('Student Breakdown'!\$G\$10:\$G\$272, "Y", 'Student Breakdown'!\$I\$10:\$I\$272, "A*")

I'm not sure, but I think the fact that one of your target grades has an asterisk (A*) is working as a wild card in this instance. You can test it out by changing one of the "A*" grades to "A+" and you should still get the proper totals. An interesting side benefit!

-
Fantastic - thanks. – dunc Jan 9 '13 at 16:47

I recommend F106dart's answer but to generalise a little you can get an "OR" type effect with one condition in COUNTIFS like this

=SUM(COUNTIFS(Range1,"x",Range2,{"a","b"}))

[although you wouldn't do that with "A" and "A*" because the * acts as a wildcard as pointed out so you'd get double counting]

COUNTIFS is only available in Excel 2007 or later so for any version you can use SUMPRODUCT like this

=SUMPRODUCT((Range1="x")*(Range2={"a","b"}))

That latter version will work with "A" and "A*" because * is not treated as a wildcard in that formula.

Assumes Range1 and Range2 are columns of data

-
Nice trick with the array {"a", "b"} ! I need to get better at using that technique. – F106dart Jan 9 '13 at 17:34

I believe this works as well:

=SUM(IF(G10:G272="Y",IF(I10:I272="A",1,0)))+SUM(IF(G10:G272="Y",IF(I10:I272="A*",1,0)))

It produces its result by using two passes at the conditional, one for A and one for A*.

-
Thanks for your efforts chuff but I haven't been able to get that to work - the result always appears as 0. I've used F106dart's answer instead. – dunc Jan 9 '13 at 16:48
It's an array formula that needs to be entered with CONTROL-SHIFT-ENTER. Sorry I failed to mention that! – chuff Jan 9 '13 at 18:41