seeking assistance for Ms Excel functions to work out breakdown per month, the number of products in a monthly total

[qeteshdlwy]

HI there

I am trying to figure out how I can use the Ms Excel functions to work out a breakdown of the items in the table.

	A	B	C
1	Date	product type	quantity
2	1/11/2013	chair	1
3	1/11/2013	chair	1
4	12/11/2013	chair	1
5	17/11/2013	stool	2
6	29/11/2013	stool	6
7	30/11/2013	chair	1
8	9/12/2013	stool	1
9	17/12/2013	stool	1
10	23/12/2013	stool	3
11	23/12/2013	stool	1
12	30/12/2013	chair	2
13	30/12/2013	chair	1
14	3/1/2014	chair	1
15	6/1/2014	chair	1
16	6/1/2014	stool	1
17	19/1/2014	stool	3
18	26/1/2014	stool	1
19	31/1/2014	chair	1
20	1/2/2014	chair	1
21	4/2/2014	chair	1
22	4/2/2014	chair	1
23	8/2/2014	chair	2
24	17/2/2014	stool	1
25	20/2/2014	stool	1
26	23/2/2014	chair	1
27	24/2/2014	chair	4

<tbody>
</tbody>

Basically I want to obtain the breakdown per month, the number of chairs and stools in a monthly total.

· IF “chair” and “stool” fall with February 2014, to calculate the relating value in C.
· IF “chair” and “stool” fall with November 2013, to calculate the relating value in C.

It has been a challenge for me and I have tried: =SUMIF(B2:B27,"chair", C2:C27) And only been able to calculate the number of chairs only, but couldn’t find a way to add the condition to include both “chair” and “stool”. I have also tried: =SUMPRODUCT((YEAR(A2:A27)=2014)+0,(MONTH(A2:A27)=2)+0,(C2:C27="chair")+0) Which only did a count of occurrences of “chair” in B, in the specified month and year.

<tbody>
</tbody>

I hope someone can guide me in working this out.
Your kind assistance and very much appreciated.

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=SUMPRODUCT(--(YEAR(A2:A27)=2014),--(MONTH(A2:A27)=2),((B2:B27="chair")+(B2:B27="stool")),C2:C27)

The + behaves like OR.

 

[qeteshdlwy]

hi
many thanks Andrew, it works now