Forum Discussion
IFS Compatibility Issues with Older Excel Versions
There was a mistake in the original SUMPRODUCT formula. Thank you for pointing this out.
First recommendation would be that those users update their Excel.
IF that's not feasible for some reason, nested IF conditions can often do what IFS does more readily. Those can get really complicated.
Another way around lots of IF and IFS conditions is to use a table and VLOOKUP for alternative values. It depends on what the consequences of the various IFS conditions are.
COUNTIFS has been around for a lot longer (since Excel 2007). I'm surprised if they're still experiencing difficulties from that specifically.
COUNTIFS($E$19:$E$700,E19,$L$19:$L$19,">"&L19)+1)
In another column, it's actually the RANK.AVG formula that results in the #NAME error. I wonder if there's an alternative to that?
- Without seeing exactly what you're dealing with it's hard to say. One thought: instead of building conditions into the COUNT function, use a helper column or two in conjunction with the original data, and then just a simple COUNT or COUNTA based on values in the helper column.
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.