Use of EOMonth in SUMIF

[Wykeman]

We've developed a sheet for use as a Statement of Account to customers and it sums 0-30 days, 31-60 days, 61-90 days, 91- 120 days and over.

We're using the following formula: SUMIF($A15:$A31,EOMONTH(F3,-1),$F15:$F31) - for 0-30 days, the others are (F3,-2) etc -, where F3 is the statement date, and can be anydate, column A has the invoice dates and column F has the invoice values. We normally invoice on the last day of the month and this formula works perfectly and picks up the invoices in the correct places on the statement.

Occasionaly we invoice in the middle of the month and this formula doesn't recognise the invoices at all and returns nothing. We've tried replacing EOMonth with (>F3-30) and various other configurations, but to no avail.

Can anyone help in resolving this problem for an Excel Dummy?

Thanks

[daddylonglegs]

I think this depends what you want to sum. If F3 is a date in July do you just want to sum everything in June or would it depend on F3. Would you sum the invoice for 15th June if F3 was 31st July?

If you genuinely want to SUM everything between 0 and 30 days before F3 you could do that like this

=SUMPRODUCT(($A15:$A31>=F3-30)*($A15:$A31<=F3-0),$F15:$F31)

change 0 and 30 for other ranges

[Wykeman]

Thank you for the very prompt response and excellent solution, perfect!