SUMIF with two criteria

[Guest]

Here's the senario. Column A has dates of sales. Column B has the sales
person's name. Column C has the amount of the sale. What I am looking for
is a sumif statement that will total the sales in column C by salesperson in
between specific dates. So for example, I want all the sales for John Smith
totaled between the dates of 4/1/05 and 4/30/05. I've tried a bunch of
different things but have had no luck getting this to work. Any help is
appreciated.

 

[Domenic]

Try...

=SUMPRODUCT(--($A$2:$A$10>=DATE(2005,4,1)),--($A$2:$A$10<=DATE(2005,4,30)
),--($B$2:$B$10="John Smith"),$C$2:$C$10)

OR

=SUMPRODUCT(--($A$2:$A$10>=D1),--($A$2:$A$10<=E1),--($B$2:$B$10=F1),$C$2:
$C$10)

....where D1 contains the start date, E1 contains our end date, and F1
contains the salesperson of interest. Adjust the ranges accordingly.

Hope this helps!

 

[Guest]

Hi,

e.g. =SUMPRODUCT(--(A1:A100="John
Smith"),--(B1:B100>=38443),--(B1:B100<=38472),C1:C100)

Note:
(1) The dates entered ar the date values, you can refer to cells with the
specific dates within.
(2) SUMPRODUCT() cannot cope with whole columns hence why I have specified
row reference within it.

Regards,
A

 

[Guest]

That's it exactly. It was the DATE(yyyy,mm,dd) that was screwing me up.
Thanks!