Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

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

Sample data

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,

share|improve this question

3 Answers 3

up vote 6 down vote accepted

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!

share|improve this answer
    
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

share|improve this answer
    
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*.

share|improve this answer
    
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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.