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

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


--
Regards,

Peo Sjoblom



"FPJ" wrote in message
...
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

=SUMPRODUCT(--(month(a1:A100)=2),--(B1:B100="washers"))
Do NOT use full column with Sumproduct:
=SUMPRODUCT(--(month(A:A)=2),--(B:B="this will not work"))
Details at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"FPJ" wrote in message
...
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

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

"FPJ" wrote:

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

=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.

FPJ wrote:

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

--

Dave Peterson

Thanks to all of you, guys. The formula worked again.

"FPJ" wrote:

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