Sumif formula across dates not working correctly

**Vaslo** · 11-23-2014, 05:09 PM

I have attached a piece of a KPI spreadsheet I am working from. Basically, I want to sum up YTD and YTD average based on the month I am in. I don't know why the formula won't work. I expected that when I chnaged the month (Cell U4), any month less than that month would be summed (T12) and give me the average based on the month I present. Instead, if I make it less than the month it gives me a value of zero, and if I say greater than it gives me the whole year.

What's wrong here?

Thanks!

Broken YTD.xlsx

Also, anyone know how to delete all those unused styles??? A consequence of copying a worksheet into this copy and now its bogging the file a little.

Thanks!!

**teylyn** · 11-23-2014, 05:40 PM

Hello,

your date formats are not consistent. In U4 you have 1-July-2014, but in F4 you have 11-Jan-2014, not 1-Nov-2014. Or maybe that's just because DATEVALUE() comes up with a different result because of my regional settings.

To avoid this risk, use Date() instead of Datevalue(), because it is unambiguous. In F4 use

=DATE($C$6-1,F2,1)

Copy to G4. Then isn H4 use

=DATE($C$6,H2,1)

and copy to the right.

Now the result will be 0 because U2 is looking at 1-July-2014. Change it to 1-Jul-2015 and the result is correct.

cheers, telyn

**Vaslo** · 11-23-2014, 06:17 PM

Worked like a charm. Thank you - don't know why I went with that date value - was more due to the solution I found for the concat I had to do. This is working great and DATE is easier for me to follow.

Repped.