totals for specific dates formula

The root cause of your problem is that you are summing from row 2 which does not contain valid numerical data. While most of the SUM functions will automatically convert non-numeric values to 0, the + addition operator does not. As a result, the expression H2:H100+I2:I100 evaluates to a 99 element array and the first value in the array is an error value. Error values are not automatically converted to 0. Consequently, the entire expression evaluates to an error.

The simplest solution is to change the starting row of each array expression to row 3. The following formula produced the intended results.

=SUMPRODUCT((H$3:H$100 + I$3:I$100) * (TEXT(G$3:G$100, "mmmm") = A$32) * (DAY(G$3:G$100) >= 1) * (DAY(G$3:G$100) <= 7))

By adding the $ to the cell references, I was able to copy this formula from B34 down to B37 and change only the starting and ending DAY values for the other three rows. B37 evaluated to -268,119.09 as expected.

You can also copy these four rows to B43:B46 and change only the TEXT value from A$32 to A$41 in each. B44 evaluates to 1,019,706.86.

Hi @Megan F, 

In the example workbook, the most reliable approach is to store the month headings as real dates and use SUMIFS to total columns H and I within each displayed date range. 

1. Make the month headings real dates  Instead of entering September as text in A32, enter:  9/1/2026  Then format A32 with the custom number format: mmmm  Do the same in A41 using 10/1/2026. The cells will still display September and October, but Excel will retain the month and year. This also avoids potential #VALUE! errors caused by dates stored as text.  
2. Formula for September 

Enter this in B34, then fill it down through B37: 

=SUMIFS($H$3:$H$1000,$G$3:$G$1000,">="&EOMONTH($A$32,-1)+CHOOSE(ROWS($B$34:B34),1,8,15,22),$G$3:$G$1000,"<"&IF(ROWS($B$34:B34)=4,EOMONTH($A$32,0)+1,EOMONTH($A$32,-1)+CHOOSE(ROWS($B$34:B34),8,15,22,29)))+SUMIFS($I$3:$I$1000,$G$3:$G$1000,">="&EOMONTH($A$32,-1)+CHOOSE(ROWS($B$34:B34),1,8,15,22),$G$3:$G$1000,"<"&IF(ROWS($B$34:B34)=4,EOMONTH($A$32,0)+1,EOMONTH($A$32,-1)+CHOOSE(ROWS($B$34:B34),8,15,22,29))) 
 

1. Formula for October 

Enter this in B43, then fill it down through B46: 

=SUMIFS($H$3:$H$1000,$G$3:$G$1000,">="&EOMONTH($A$41,-1)+CHOOSE(ROWS($B$43:B43),1,8,15,22),$G$3:$G$1000,"<"&IF(ROWS($B$43:B43)=4,EOMONTH($A$41,0)+1,EOMONTH($A$41,-1)+CHOOSE(ROWS($B$43:B43),8,15,22,29)))+SUMIFS($I$3:$I$1000,$G$3:$G$1000,">="&EOMONTH($A$41,-1)+CHOOSE(ROWS($B$43:B43),1,8,15,22),$G$3:$G$1000,"<"&IF(ROWS($B$43:B43)=4,EOMONTH($A$41,0)+1,EOMONTH($A$41,-1)+CHOOSE(ROWS($B$43:B43),8,15,22,29))) 
 

The formula automatically calculates: 

• 1st through 7th 
• 8th through 14th 
• 15th through 21st 
• 22nd through the actual final day of the month 

Based on the sample data, the September 22nd through month-end result should be-268,119.09, while the October 8th through 14th result should be 1,019,706.86. 

Make sure the values in column G are genuine Excel dates, not text. Also, all SUMIFS ranges must have matching dimensions, such as rows 3 through 1000 for columns G, H, and I, because inconsistent range sizes can cause #VALUE!.  Thank you again for your time and understanding.  I really appreciate your patience, and I’m here to help. Looking forward to your response.   

Hi,

In cell B34, enter this formula

=BYROW(A34:A37,LAMBDA(z,LET(a,1*(REGEXEXTRACT(TEXTSPLIT(z,"-"),"\d+",1)&"/"&A$32&"/2026"),SUM(($G$3:$G$7>=INDEX(a,1,1))($G$3:$G$7<=INDEX(a,1,2))($H$3:$H$7+$I$3:$I$7)))))

Hope this helps.

If it were me, I would reconsider the output layout.

Instead of complex formulas scattered all over, I would have 1 simplier formula.

General idea would be like this: Again, just my preference.

Hi @Megan F ,

Try below formula in cell B34.

=LET(
     wk, A34:A37,
     dt, G3:G7,
   calc, MAP(REGEXEXTRACT(wk, "\d+", 1), REGEXEXTRACT(TEXTAFTER(wk, "-"), "\d+", 1),
             LAMBDA(a,b, SUM(BYROW(TRANSPOSE(DATE(UNIQUE(YEAR(dt)), MONTH(A32 & 1),
                    SEQUENCE(b - a + 1,, a)) = TRANSPOSE(dt)), OR) * BYROW(H3:I7, SUM)))),
         VSTACK(calc, "", SUM(calc))
)

and this one in cell B43.

=LET(
     wk, A43:A46,
     dt, G3:G7,
   calc, MAP(REGEXEXTRACT(wk, "\d+", 1), REGEXEXTRACT(TEXTAFTER(wk, "-"), "\d+", 1),
             LAMBDA(a,b, SUM(BYROW(TRANSPOSE(DATE(UNIQUE(YEAR(dt)), MONTH(A41 & 1),
                    SEQUENCE(b - a + 1,, a)) = TRANSPOSE(dt)), OR) * BYROW(H3:I7, SUM)))),
         VSTACK(calc, "", SUM(calc))
)

Note: The month of September has 30 days and not 31 days as you have entered in the data of cell A37. So instead of the number 31 you will need to enter the number 30 in this cell.

HTH

IlirU