•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Why is my COUNTIFS not counting correctly?
Our take
Are you frustrated that your COUNTIFS formula isn’t delivering the expected results? You’re not alone—many users encounter similar challenges when comparing columns with specific criteria. In your case, you're counting instances where column A equals 3 and column G is less than column F, yet the output is lower than anticipated. This often happens due to overlooked data nuances, like row 28 in your example. Let’s dive deeper to uncover why adjusting that value can change your results and ensure accurate counting in your spreadsheets.
I've linked the data here: https://i.imgur.com/UHC2ngj.png
I am comparing two columns, include in the count if both:
column A fund = 3
column G is less than column F
I expect an output of 2, but I'm getting 1. I am assuming it is not counting row 28.
Weirdly if I change G28 in the raw data to a value ~2.1 or less, it will correctly pick up in the count.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Anomalous Sorting of COUNTIF ColumnsEDIT: 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. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 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. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 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. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 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 submitted by /u/LikeCCClockwork [link] [comments]
- COUNTIFS not working correctly and I'm just confused on what to do.I am trying to learn the COUNTIF formula. =COUNTIFS($S$13:$S$61, ">=95", $S$13:$S$61, "<=97") The function counted that there are 2 that fills this criteria; however, I counted it manually and there should be 3 instead. The same goes for other formulas I inputted. I watched Youtube tutorials and read online forums but I still don't get what is wrong with the function. I use Excel 2019. What can I do to improve or solve it? https://preview.redd.it/z08fpbj2olpg1.png?width=442&format=png&auto=webp&s=fb7eeedffeb55ef7649733465c3006b029b9de1b submitted by /u/LoveMilfsEveryday007 [link] [comments]
- COUNTIFS not recognizing TRUE valuesI feel like I’m crazy. I have a simple COUNTIFS function that is returning 0 for every line. At first, the values for column K were lowercase true/false but I’ve struggled with that in the past so I corrected the data to uppercase. No matter whether I use TRUE or “TRUE” in the function, it always returns 0. I even tried a simpler =COUNTIF(K:K,TRUE) (also tried “TRUE”) and it’s not working either. What am I overlooking here? https://preview.redd.it/98a5z974dvjg1.png?width=447&format=png&auto=webp&s=3ce88c79b2cd31eb5f9e35c3879f8972f96de305 submitted by /u/rachelsquito [link] [comments]