Adjusting Sum Product for Date

**erock24** · 02-20-2007, 12:12 PM

I have this formula: SUMPRODUCT(--($D$7:$D$19-DAY($D$7:$D$19)+1=K$5)*($J$5=$I$25)*($J$7:$J$19))

The formula works great. My problem is that K5 has to be the 1st day of the month for it to work. I need it to be the last day of the month, but I don't know how to modify the formula.

The goal of the formula is to sum all numbers in my "J" range if the dates in my "D" range equal the same month/year as my "row 5" guy or in this example K5. I also equated J5 with I25 in order to complete the sum product formula.

Thank you for your time and help

**oldchippy** · 02-20-2007, 12:17 PM

This will change your date to the 1st of the month

=DATE(YEAR(A3),MONTH(A3),1)

**erock24** · 02-20-2007, 12:24 PM

I need K5 to read the last day of month (ex. 1/31/2007)

So How would I include your formula in mine to convert the 1/31/2007 to 1st of the month thus making the original formula work, but leaving K5 showing the last day of the month.