Forum Discussion
IFS Compatibility Issues with Older Excel Versions
There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.
I'm not totally sure that I follow that re: the helper column, but here's an example of what I'm working with. The goal is to rank schools by % poverty in two ways: first, across all schools in the district, and second only in comparison to other schools that serve the same grade span. The user enters the % of students who are low income and then the rankings happen based on that. Both of these functions produce the #NAME error for certain users.
For the district-wide rank, I think I can just use RANK. For the Grade-span rank, that's where I have the COUNTIFS. The formula is =IF($D2=0,"",COUNTIFS($C$2:$C$14,C2,$D$2:$D$14,">"&D2)+1)
D2 = the user entered value for Low income. Basically, if that is blank, I want to hide the formula.
So this grade-span rank is the one causing me the problem in particular.
| School | Type | Grade Span | % Low Income | District-Wide Rank | Grade-Span Rank |
| School A | Elementary | P-8 | 77 | 8 | 1 |
| School B | High | 9-12 | 75 | 10 | 3 |
| School C | High | 9-12 | 76 | 9 | 2 |
| School D | High | 9-12 | 88 | 4 | 1 |
| School E | Middle | 6-8 | 97 | 1 | 1 |
| School F | Middle | 6-8 | 87 | 5 | 2 |
| School G | Elementary | K-5 | 55 | 12 | 3 |
| School H | Elementary | K-2 | 89 | 3 | 1 |
| School I | Elementary | P-5 | 90 | 2 | 1 |
| School J | Elementary | K-5 | 86 | 6 | 1 |
| School K | Elementary | 3-5 | 84 | 7 | 1 |
| School L | Elementary | K-5 | 40 | 13 | 4 |
| School M | Elementary | K-5 | 58 | 11 | 2 |
Maybe SUMPRODUCT works for all users. This formula should be available since Excel 2007.
Wow, thanks! Yes, that does seem to work pretty well. There's an issue if there's a tie, though. Example: if school C and D had the same low income percent, they both get the rank of 2 with SUMPRODUCT vs 1 with COUNTIFS. Wonder if there's a solution.
School Type Grade Span % Low Income District-Wide Rank Grade-Span Rank School A Elementary P-8 77 9 1 1 School B High 9-12 75 10 3 3 School C High 9-12 88 4 1 2 School D High 9-12 88 4 1 2 There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.
Ah yes, the same +1 solution as with the COUNTIFS formula I had. Thanks very much for the help!