Thread: Sum based on Cell criteria and date range

Afternoon all,

In the attached workbook I am looking for a formula to help extract the total dollar amounts associate with a plant area for a given month. The Data tab contains mock information from a system dump.

On the Monthly Totals tab I would like to be able to extract the total dolloar amount form L:L on 'Data' for a given month based on a Matching Plant section in A:A.

I have the expected total in 'Monthly Totals'

Data!F3 should be 1/20/2011 not 5/5/1900

Thanks

Here it is with the Abs Ref applied and unnecessary sheet ref removed for cell C7 and you can copy throughout

=SUMIFS(Data!$L$1:$L$20,Data!$A$1:$A$20,"="&LEFT($B7,2),Data!$F$1:$F$20,">="&C$4,Data!$F$1:$F$20,"<="&EOMONTH(C$4,0))

I didn't notice the values in col A so if you want you can refer to those instead of the part that reads LEFT($B7,2)