•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Issue with sum count in mirrored spreadsheet?
Our take
Are your formulas failing to count correctly in your mirrored spreadsheet? It’s frustrating when tools that once worked seamlessly suddenly don’t, especially after making adjustments like adding new rows and columns. Your use of `SUMPRODUCT` and `ISTEXT` is on the right track, but there may be nuances in how the data is structured that are causing the zero results. Let’s dive into potential issues and solutions to ensure your counts reflect the true completion status of your game combos.
Hi, I've been trying to deal with a thing to count completion for two-mod combos in a game my friends and I play. It's supposed to be mirrored, and it's worked until I expanded the sheet by three rows and columns. I have the following formulas to count the cells that have text that isn't a formula
=SUMPRODUCT( --ISTEXT(B2:CD82), --IFERROR(FORMULATEXT(B2:CD82)="", TRUE) )
and the following to count everything that doesn't have a formula
=SUMPRODUCT( --(B2:CD82<>""), --IFERROR(FORMULATEXT(B2:CD82)="", TRUE) )
But it's not working, it's just saying both are 0. What am I doing wrong? Here's an image of the spreadsheet, sans the calculated part: https://imgur.com/a/SJ4qZEd
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]
- Blank cells are being summed as a value greater than five using a SUM(IF functionHi! I have a large dataset of 7-day schedules that I am summing to calculate breaks. The function is setup like this: =SUM(IF(CELL1>5, 0.5, 0)), (IF(CELL2>5, 0.5, 0)) and so on until all seven days are tabulated. Because a full time person would only work 5 days a week, at least 2 days are blank per row, but we're open all 7 days and I want to sum the entire spreadsheet, so I have to count all 7 days. A full time person should have 2.5 hours per week. For some reason the function is mostly working, but about a third of the results are larger than they should be because it's counting some blank cells as greater than 5. For example, a part time person working only 3 days should have 1.5 break hours, but the function returns 3.5 because it is counting all the blank cells as containing a value > 5. What's really strange is it is only doing this some of the time. Every row has at least two blank cells, but only about a third of the sums are wrong. I can't figure out why. The columns are all formatted as a number. The value does update correctly if I manually enter a "0" in the blank cells, but this is a very large dataset and that would take forever. Thoughts? submitted by /u/andylefunk [link] [comments]
- Excel formula automatically rewriting itself??Hi all, this is a really odd problem to run into and I am unsure how to proceed from here. I am currently entering data and I have Excel performing an =SUM(B5:AC5) for my data that is being entered. Because the data entry can go pretty fast sometimes, I would keep entering data past AC5 before I realize what happened. When I look at my SUM column, Excel has flagged my =SUM(B5:AC5) formula with the error below, which is fine and whatever, but it is automatically changing my =SUM(B5:AC5) formula to =SUM(B5:AF5) or something of similar depending on what column I stopped at. Just for reference, data from AD5 to AO5 is being summed up in another column with SUM=(AD5:AO5). Formula error being flagged by Excel I have no idea why it is doing this and I tried to ignore error, but it still automatically rewriting my formula anyways! This worksheet and formula worked smoothly 4 months ago so I don't know what is going on. submitted by /u/WildKhanine [link] [comments]
- 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]