•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Sum formula for calculating month occurrences from dates works for everything except for January.
Our take
It sounds like you’re encountering a common challenge when automating month occurrence calculations in your spreadsheet. While your formula successfully tracks occurrences for most months, it seems to misinterpret blank cells as "1" specifically for January. This issue likely arises from how the formula processes empty entries. To resolve this, you might consider revising your conditional statement to accurately handle blanks. Ensuring clarity between Excel and Google Sheets formulas can also help refine your approach. Let's explore a solution that simplifies your data management effectively.
I am attempting to automate the calculations of how often each month is recorded in another tab. The formula is working for all the other months, but as I am using this template to calculate values as I enter them, I arbitrarily set the limit to 2000, allowing me not to have to think about changing the formula as I enter new information. Unfortunately, it seems that the blank cells are being coded as "1"
I have tried going through the formula and found that all the blank cells are coding themselves as "true" only for the month of January (1). I attempted to add
=IF(SHEET2!A3:A2000 = " ", 0, ...
to the beginning of the formula, but it just gave me a spill error. I might be getting confused between excel vs. Google Sheets formulas.
Thank you!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- Monthly tracking workbook I use to track employee sales metrics; Trying to find a way to make the process less labour intensiveTruly having a hard to describing my issue effectively but hoping someone can help. First time posting here and I'm by no means an expert with excel, so please be kind! I have a monthly workbook where I track each employees revenue and other metrics. Every 2 weeks for payroll, I provide a print out of these numbers, and the payroll sheet pulls data from multiple sheets in the workbook. For example, every workbook has a separate sheet for each day of the month, titled "1" through "31". I have pay period sheets, so I'll use one titled "04.02.26 - 04.15.26". Then I'll have the data for each employee pulled from multiple sheets. For example, I use the formula =SUM('2:15'!E3) to pull the sales data from each day of that period for the specific employee. This works quite well. However, when I create a new month's spreadsheet, I have to manually alter this formula for each employee and for each data point (more than just revenue, at least 6 different data points for 7 employees). Is there a way to automate this? For example, a cell or two where I'm able to enter the date range and all of the formulas update to that date range for the corresponding pages? I'm sorry of this post is confusing. Truly it's confusing even typing it! submitted by /u/Ok_Smile9222 [link] [comments]
- Trying to calculate the difference in dates using =IF Formula. I am new to ExcelHi, totally new to excel, trying to apply formulas from a previous excel sheet to another. Hello, I'm very new excel and not very good at it. I work a corporate job and I'm trying to calculate the days between two dates(so if M is 1/1/2026 and O is 1/2/2026 i need the difference in column N). I have a formula from a previous spreadsheet and I keep trying to apply it to the new spreadsheet but it keeps coming back as #VALUE!. I also put the formula in Copilot and I can't forgive the life of me figure out the formula or why its not working. I also need for the whole column with different dates. the formula from a previous spreadsheet is =IF(ISBLANK(E139)" "IF(ISBLANK(T39),TODAY()-E139T139-E139)) Hopefully this makes sense. I'm honestly not great with Excel and trying my best. If someone could help that'd be great. Thankyou so much! EDIT: You all have been wonderful! TY!!! submitted by /u/jensinoutaspace [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]
Tagged with
#Excel alternatives for data analysis#AI formula generation techniques#formula generator#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#google sheets#Excel alternatives#rows.com#sum formula#month occurrences#January#blank cells#automate calculations#Excel#Google Sheets#spill error#IF function#calculating values#template