***I realize I'm posting something that may be required to be split into two threads; I suspect that the problems are related, and figured it might be easier to diagnose with both problems listed. Let me know if I need to split or retract one of items.***
Hello,
I am tabulating FBS college football data from 2023 into a single worksheet, and I'm experiencing something strange with some of the columns and their filter dropdowns; they are not being counted correctly and are not displaying in numerical order where I have listed teams' rankings. Here, I have selected only BJ2, BP2 and BV2 (non-zero values in row 2 that I want to count as a total of 3), but the sum of these listed at bottom is not correct:
And if I click on the filter dropdown of column T, the values are all listed, but they are not in order:
I did use some fairly elaborate formulas (for me, anyway) when I was building this--IFERROR, SUM(COUNTIFS, VLOOKUPs nested inside of IFs, etc.--but have since copied and pasted the relevant cells as values to avoid the file being too bloated and slow. The closest I've been able to come is a count of 6 (!), using a formula of:
Also, because it's just the AP top 25 rankings for 133 schools, most rows have a zero in the relevant cells; I am using a custom format in these columns to not display zeroes in many columns:
...but changing it from the custom format to "General" or to "Number" did not eliminate the problem. Neither did performing a "Replace All" of "0" with "".
Happy Friday to whomever is reading this.
Hello,
I am tabulating FBS college football data from 2023 into a single worksheet, and I'm experiencing something strange with some of the columns and their filter dropdowns; they are not being counted correctly and are not displaying in numerical order where I have listed teams' rankings. Here, I have selected only BJ2, BP2 and BV2 (non-zero values in row 2 that I want to count as a total of 3), but the sum of these listed at bottom is not correct:
And if I click on the filter dropdown of column T, the values are all listed, but they are not in order:
I did use some fairly elaborate formulas (for me, anyway) when I was building this--IFERROR, SUM(COUNTIFS, VLOOKUPs nested inside of IFs, etc.--but have since copied and pasted the relevant cells as values to avoid the file being too bloated and slow. The closest I've been able to come is a count of 6 (!), using a formula of:
Excel Formula:
=SUM(--(VSTACK([@[ W0 Ranking]],[@[ W1 Ranking]],[@[ W2 Ranking]],[@[ W3 Ranking]],[@[ W4 Ranking]],[@[ W5 Ranking]],[@[ W6 Ranking]],[@[ W7 Ranking]],[@[ W8 Ranking]],[@[ W9 Ranking]],[@[ W10 Ranking]],[@[ W11 Ranking]],[@[ W12 Ranking]],[@[ W13 Ranking]],[@[ W14 Ranking]],[@[ W15 Ranking]],[@[ W16 (CC) Ranking]],[@[ W17 (B) Ranking]],[@[ W18 (F4) Ranking]],[@[ W19 (NC) Ranking]])>0))Also, because it's just the AP top 25 rankings for 133 schools, most rows have a zero in the relevant cells; I am using a custom format in these columns to not display zeroes in many columns:
...but changing it from the custom format to "General" or to "Number" did not eliminate the problem. Neither did performing a "Replace All" of "0" with "".
Happy Friday to whomever is reading this.
