Summing based on multiple criteria

**Environment** · 08-23-2011, 10:06 AM

Hi Everyone,

Can somebody please help me with this as its driving me nuts and I’m very close to launching my laptop out of the window.

In a nut shell this is what I am trying to achieve,

I want Excel to SUM coulomb H where,

the date in coulomb C is greater than (Ref to cell with date entered)
the date in coulomb C is less than (ref to cell with date entered)
Coulomb E = (Ref to cell with name in)
& Coulomb F = (Ref to Cell with name in)

Hope that all makes sense, very quick draft copy attached.

Thanks for any help in advance.

E

**NBVC** · 08-23-2011, 10:14 AM

If you are using XL2003, you need SUMPRODUCT:

e.g.

=SUMPRODUCT(--('Data Paste Sheet'!$C$8:$C$3000>=D2),--('Data Paste Sheet'!$C$8:$C$3000<='Selection Sheet'!D3),--('Data Paste Sheet'!$E$8:$E$3000=D4),--('Data Paste Sheet'!$F$8:$F$3000=D5),'Data Paste Sheet'!$H$8:$H$3000)

You cannot use whole column references with SUMPRODUCT in XL2003 or prior, so adjust ranges so that you have the minimum required number of columns.

If you have XL2007, you can use SUMIFS

=SUMIFS('Data Paste Sheet'!$H:$H,'Data Paste Sheet'!$C:$C,">="&D2,'Data Paste Sheet'!$C:$C,"<="&'Selection Sheet'!D3,'Data Paste Sheet'!$E:$E,D4,'Data Paste Sheet'!$F:$F,D5)