Thread: SUMIF 3 variables

Hi

I need to sum (or ideally average) the numbers in column B when teh dates in Column A range between two referenced variables (say 1st Jan and 5th Jan) AND the values in column B are >0 (some are negative numbers).

A B
Order Date Unit price
01-Jan £16.25
01-Jan £4.95
01-Jan -£10.00
04-Jan £35.00
05-Jan £35.00
05-Jan -£10.00
07-Jan £39.25

Date #1 01-Jan
Date #2 05-Jan

To sum:

=Sumproduct(--(A1:A100>=Startdate),--(A1:A100<=Enddate),--(B1:B100>0),B1:100)

To Average:

=Sumproduct(--(A1:A100>=Startdate),--(A1:A100<=Enddate),--(B1:B100>0),B1:100)/Sumproduct(--(A1:A100>=Startdate),--(A1:A100<=Enddate),--(B1:B100>0))

Replace StartDate and Enddate with cell references indicating those dates...

adjust ranges to suit.

If you are using Excel2007

To Sum:

=Sumifs(B1:B100,A1:A100,">="&Startdate,A1:A100,"<="&Enddate,B1:B100,">0")

To Average:

=Averageifs(B1:B100,A1:A100,">="&Startdate,A1:A100,"<="&Enddate,B1:B100,">0")