•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
For some reason my COUNTA function is returning one too many results (also how to quickly fill many cells with sequential numbers?)
Our take
Are you finding that your COUNTA function is unexpectedly returning one too many results? This common frustration often stems from how the function interprets data, especially when starting from a cell labeled zero. Understanding this nuance can help you achieve the accurate count you need. Additionally, if you're looking for a quick way to fill cells with sequential numbers without premium tools, there are accessible methods to streamline this process. Read on to discover effective strategies that can enhance your spreadsheet experience today.
I have COUNTA to read from A2, to 502, since that goes up the the '500' that I need. However, for some reason, the function is returning 501. The first cell it's reading, a2, starts at 0 (labeled with the number zero), then up to cell 502, which is labeled 500.
Also, for desktop/free use, is there a faster way to do sequential numbering. I had a tough time doing the =sequence function, and then I don't have access to some of the other fill tools, and since I'm just learning, I don't have the money for a subscription right now.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What formula for sequencing number but when adjacent row is blank it wont get sequencedI have tried this formula form chat gpt: =IF(B14="","",COUNTIF($B$14:B14,"<>")) =IF(B14="","",SUBTOTAL(103,$B$14:B14)) =IF(B14="","",SUBTOTAL(103,B$14:B14)) But the blank row still get counted like: B1 has data, number 1 B2 has no data, blank B3 has data, number 3 What i want is: B1 has data, so its number 1 B2 has no data, so its blank B3 has data, so its number 2 Iam using google spreadsheets, sorry if it confusing iam still new here *edit: thanks for the help everyone i think the previous formula not doing what i wanted is because the cell that i thought blank is filled with vlookup formula, iam using =IF(B14 = "", "", MAX(A$13:A13) + 1) and it work! submitted by /u/Barusan_buat [link] [comments]
- =COUNTA() is returning 0 even when some cells in the range aren't blankSee image for reference. In E136 I've got the COUNTA formula. In the cells immediately above it I've entered some dummy values by hand. The COUNTA formula should at the very least return 4 based on this setup, but it's 0. I've tried repairing the file but that didn't do anything. Claude didn't produce anything helpful. Does anyone know why this is happening? EDIT: Moved the formula to another column to avoid circular referencing. Still showing 0. EDIT 2: There was an unrelated circular reference in a cell in column D that was apparently affecting column E. Once I removed the circular reference in column D, column E fixed itself. https://preview.redd.it/q7pqgdb8kusg1.png?width=457&format=png&auto=webp&s=cb90faf2035dbbcfd639f90bb010da5817861fbb submitted by /u/WorkSpeed [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]
- 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]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#rows.com#financial modeling with spreadsheets#self-service analytics tools#machine learning in spreadsheet applications#Excel compatibility#business intelligence tools#row zero#collaborative spreadsheet tools#real-time data collaboration#COUNTA#Excel#sequential numbers#function#sequence function#desktop#cell#free use