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

diderooy

New Member
Joined
Jan 9, 2014
Messages
29
Office Version
  1. 365
Platform
  1. Windows
***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:

1715347265558.png


And if I click on the filter dropdown of column T, the values are all listed, but they are not in order:

1715346926403.png


1715346949133.png


1715346959619.png


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:

1715347399898.png


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


1715347974226.png
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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)
 
Upvote 0
Solution
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!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top