Conditional sum

[mbsalim]

Hi,

I've got a workbook for budget variance but need the total budgeted hours to reflect only up to the week beginning of todays date as opposed to the whole year so trying the following formula;

=SUMIF(C5:AH5,IF(A3>=C5:AH5,1,0),C9:AH9)

where C5:AH5 = week begin dates
A3= =TODAY()-WEEKDAY(TODAY())+2 ... (monday of every week) - this would match the dates in C5:AH5
C9:AH9 the budgeted hours that need to be summed

but the formula seems to be failing as it is giving me a zero as opposed to the actual figure

thanks!!!!

 

[Aladin Akyurek]

IF(A3>=C5:AH5,1,0) delivers 1 and/or 0's, not dates which can serve as criteria for C5:AH5 = week begin dates.

 

[mbsalim]

Oh ok, so what would be the solution for this?

 

[Jonmo1]

Try

=SUMIF(C5:AH5,"<="&A5,C9:AH9)

 

[Aladin Akyurek]

Oh ok, so what would be the solution for this?

Better wording the criterion would help... Is this:

=SUMIFS(C9:AH9,C5:AH5,">="&A3)

what you are after?

 

[mbsalim]

thanks guys!!!! Much appreciated

=SUMIFS(C9:AH9,C5:AH5,"<="&A3)

^ worked a charm!