Cannot COUNT/SUM Displayed Table Values, Table Filtered Values Out of Order

[diderooy]

***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:

=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.

 

[Alex Blakenburg]

Typically that means the items that not being included in the "Sum" are seen as being text.
Since you have removed the formulas there are a few ways of converting it.
Given there are multiple columns involved one option would be:

1. Format all the data cells as General or Number (anything other than text)
2. Put a numeric 1 in a cell somewhere
3. with the cell with the 1 selected Ctrl+C
4. select the data cells
5. PasteSpecial Multiply then OK

See if that fixes it.

If you are used to using VBA and we can give you some code to do it. You just need to give us the data range that needs to be converted and what number format you want to use.
(In which case also tell us if you know how to use the immediate window in VBA)

 

[diderooy]

That did the trick. I guess I don't understand why the cell format was throwing them off, but I just added a step or two to clean up the extra zeros and the multiplication made the remaining integers recognized in a more expected way.

Thank you!

 

[Alex Blakenburg]

You're welcome. Glad I could help.