Sum based on dates and Conditions

**mikello** · 08-10-2009, 06:30 AM

Dear all,

Please find the attached demo file,there is some formulas on
cells e13,f13,e14 and f14,but i need the sum should be counted
based on date values which is at c 'columns.

Hint: on date 1st it should be 50 instead of 80.

Thanks in Advance,

Mike

**DonkeyOte** · 08-10-2009, 06:42 AM

Mike, one approach avoiding SUMPRODUCT and Arrays and Volatile Functions would be:

E13:
=SUMIF(INDEX(E$2:E$8,MATCH(MAX($C12:$C13),$C$2:$C$8,0)):INDEX(E$2:E$8,MATCH(MAX($C12:$C13),$C$2:$C$8)),$D13,INDEX(H$2:H$8,MATCH(MAX($C12:$C13),$C$2:$C$8,0)):INDEX(H$2:H$8,MATCH(MAX($C12:$C13),$C$2:$C$8)))

In terms of SUMPRODUCT you would use:

E13:
=SUMPRODUCT(--($C$2:$C$8=MAX($C12:$C13)),--(E$2:E$8=$D13),H$2:H$8)

which as you can see is more succinct but SUMPRODUCTs are quite hungry performance wise... the first approach is also dependent upon the data being sorted in date order.

Without knowing your final requirements you might want to think about investigating a Pivot Table based approach - see link in my sig. for general intro if unsure.