Location Score Differences Based on Dates?

[tgallag1]

On a DATA sheet we have:

Column A = location number
Column D = Review Date
Column E = Review Score

FRONT sheet we have:

Column D = Location Number
Cell N3 = previous quarter start date
Cell O3 = previous quarter end date
Cell N4 = current quarter start date
Cell O4 = current quarter end date

Need a formula for FRONT sheet which, for each corresponding location in column D, will return the Review score from the DATA sheet for the previous quarter subtracted from the Review score for that same location for the current quarter.

Thanks in advance

[benishiryo]

hi tgallag1. it's been a while you've joined & you have probably upgraded your excel version. could you update your profile of your MS Off Ver. it would be clearer & help members give you newer & more efficient formulas. also, uploading an excel file with manually keyed desired results help.

if you are using Excel 2007 and above, this will give you the results for current quarter (assuming data for location in D2)
=SUMIFS(DATA!E:E,DATA!A:A,D2,DATA!D:D,">="&$N$2,DATA!D:D,"<="&$O$2)

you just have to change a little to get the previous quarter:
=SUMIFS(DATA!E:E,DATA!A:A,D2,DATA!D:D,">="&$N$1,DATA!D:D,"<="&$O$1)

then subtract previous quarter from current

in Excel 2003, use SUMPRODUCT & do not use the full column as it slows down calculations (for current quarter again):
=SUMPRODUCT((DATA!$E$2:$E$1000)*(DATA!$A$2:$A$1000=D2)*(DATA!$D$2:$D$1000>=$N$2)*(DATA!$D$2:$D$1000<=$O$2))

[tgallag1]

Perfect, thank you for your help. And I appreciate you supplying both versions. I use 2007 but depending on the situation I sometimes need 2003 as some of our users have not converted.

Thank you!