Sum(countifs) Not picking up everything

[rikkyshh]

HI All

Im using the Sum(CountiFs) formula with multiple arrays, it has worked fine on 80% of the tables, hoever when I use 3 or more arrays it decides not to pick up everything.

The Data Dashboard has the grades,
Column AV has the grades the students have achieved,
column W has the grades that they need to achieve.
The class can be found in Column E

On the AR8 sheet im trying to populate the table using the above 3 columns, but it does not count everything despite me including it in the criteria

E.G the formula in the matrix should show that there are 12 students who are achieving a U when they are targeted at a C, but the table only counts 8...

=SUM(COUNTIFS('Y11 Data Dashboard'!$E$3:$E$3000,{"11sC3","11sC4","11sC5","11sC6"},'Y11 Data Dashboard'!$AV$3:$AV$3000,{"U-";"U";"U+"},'Y11 Data Dashboard'!$W$3:$W$3000,{"C-";"C";"C+"}))"

Please help!


Number of students Working at Grade
U G F E D C B A A*
U 0 0 0 0 0 0 0 0 0
G 0 0 0 0 0 0 0 0 0
F 1 0 0 0 0 0 0 0 0
E 0 4 3 2 1 0 0 0 0
D 3 5 9 5 1 1 0 0 0
C 8 4 6 8 4 1 0 0 0
B 0 0 0 2 0 0 0 0 0
A 0 0 0 0 0 0 0 0 0
A* 0 0 0 0 0 0 0 0 0

[Tony Valko]

I didn't download your file.

That particular syntax will only work with 2 array constants. You have 3.

Use SUMPRODUCT instead. Also, it'd be better if you use cells to hold the criteria.

A1:A4 = 11sC3, 11sC4, 11sC5, 11sC6
B1:B3 = U-, U, U+
C1:C3 = C-, C, C+

Then the formula would be:

=SUMPRODUCT(--ISNUMBER(MATCH('Y11 Data Dashboard'!$E$3:$E$3000,A1:A4,0)),--ISNUMBER(MATCH('Y11 Data Dashboard'!$AV$3:$AV$3000,B1:B3,0)),--ISNUMBER(MATCH('Y11 Data Dashboard'!$W$3:$W$3000,C1:C3,0)))