Anomalous Sorting of COUNTIF Columns
EDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it.
Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly:
https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/
(Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.)
I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint.
On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula:
=COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#)
(Where # equal the row)
The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns.
Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A#
The formula works just fine.
But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort.
So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is:
=COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28)
After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table.
HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct:
=COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2)
So.... what's the issue and how do I resolve it?
Version: Microsoft Excel for Microsoft 365 MSO (v2508)
Environment: Desktop
My Knowledge: Intermediate
[link] [comments]
Want to read more?
Check out the full article on the original site