I need help with a formula that’s driving me crazy. I’m trying to track revenue performance by comparing this month to last month, but only for the same number of days.
Let me explain what I mean. Today is the 15th of the month. I can easily calculate total revenue for these 15 days in the current month. But I also want to see what our revenue was for just the first 15 days of the previous month so I can compare them fairly.
Here’s my setup:
Column D has all the dates from last month (like 8/1/2024, 8/2/2024, etc)
Column E extracts just the day number from those dates (1, 2, 3, 4, etc)
Cell F20 contains today’s day number using =DAY(TODAY())
Column J has the actual revenue numbers I want to add up
I wrote this formula but it keeps giving me zero:
=SUMIF(E1:E31,"<="&F20,J1:J31)
When I test it with a hard-coded number like this, it works perfectly:
=SUMIF(E1:E31,"<=15",J1:J31)
This returns 245 which looks right. What am I doing wrong with the cell reference version?
This happened to me with monthly sales comparisons. Your DAY() formula in column E probably isn’t producing actual numbers - Excel sometimes treats the results as text even though they look like numbers. Quick test: click any cell in column E and check if the value in the formula bar matches what’s displayed in the cell. If there’s formatting weirdness, that’s your problem. I fixed this by recreating column E differently. Instead of just DAY(D1), I used DAY(D1)+0 or VALUE(DAY(D1)). This forces Excel to treat the extracted day as a real number, not text that looks like a number. Your hardcoded version works because you’re typing actual numbers, but the cell reference version compares numbers to text values.
I’ve hit this exact problem before - it’s usually a data type mismatch. Your SUMIF formula looks right, but Excel’s probably treating column E values differently than you think. Try wrapping your cell reference with VALUE: =SUMIF(E1:E31,"<="&VALUE(F20),J1:J31). This forces Excel to see F20 as a number, not text. Or use this instead: =SUMIF(E1:E31,"<="&F20+0,J1:J31). Adding zero converts F20 to numeric. Another thing - your DAY formula in column E might’ve returned text instead of numbers. Check by clicking a cell in column E. If it’s left-aligned, it’s text. If right-aligned, it’s a number. Text values? Convert them to numbers with VALUE() or multiply each cell by 1.
Check if F20 has trailing spaces or weird formatting. DAY() sometimes acts up with that stuff. Try =LEN(F20) to see if it’s showing 2 digits when it should be 1-2. Also try clearing the formatting on F20 and retyping the formula - that fixed the same issue for me before.