sumproduct function

F

FPJ

On the table below, I want to count how many times did we purchase washers in
February of 2007. How can I write this in SUMPRODUCT FUNCTION? Please help.
Thanks.
A B
1 1/04/05 nails
2 1/23/05 screws
3 4/05/05 screws
4 2/22/06 washers
5 6/25/06 nails
6 8/03/06 washers
7 2/15/07 washers
8 2/21/07 washers
9 2/26/07 nails
10 4/21/07 washers
11 5/04/07 washers

fpj
 
P

Peo Sjoblom

=SUMPRODUCT(--(YEAR(A1:A20)=2007),--(MONTH(A1:A20)=2),--(B1:B20="washers"))
 
T

Toppers

one way:

=SUMPRODUCT(--(B2:B100="washers"),--(MONTH(A2:A100)=2),--(YEAR(A2:A100)=2007))

I recommend you put "washers" in a cell rather than hard code it.

=SUMPRODUCT(--(B2:B100=H2),--(MONTH(A2:A100)=2),--(YEAR(A2:A100)=2007))

H2 contains "washers"

HTH
 
D

Dave Peterson

=sumproduct(--(text(a1:a11,"yyyymm")="200702"),--(b1:b11="washers"))

You may want to look at data|pivottable. It's made for coming up with this kind
of summary.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top