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.
- I'd love to ask everyone update their Excel! But unfortunately that's not possible. I think for many of the issues I could probably work out what I need with nested IF. I don't think the table and VLOOKUP would work though. In one column, I'm actually using COUNTIFS to produce a ranking based on certain criteria. In the formula below, column E has the criteria, and column L has the value I want to rank by. I don't think I could find a nested IF approach to this.
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
