1
votes

I have excel data in following format.

 Date   In Stock
2/02/16     x
5/02/16     
18/02/16    x
22/02/16    x
14/03/16    
16/03/16    
21/03/16    x
28/03/16    x
1/04/16     
7/04/16     
9/04/16     
8/04/16     x

I want to apply a formula for reporting purposes in column E. For each month, tally the total number of 'x'.

{COL D}     {COL E}                             {COL F}
Month       Number of Products In Stock         Expected Result
Feb-16                                          3
Mar-16                                          2
Apr-16                                          1

Example: For February 2016, I am expecting to see a value of '3' in cell E4.

The formula I have created below doesn't work. The first criteria is using the month function and I am looking for '2' (being February) as the value. The second criteria looks at Column B and the value 'x'.

=COUNTIFS(month(A4:A19),2,B4:B19,"x")

Any assistance greatly appreciated.

3

3 Answers

1
votes

With data in cols A and B, try SUMPRODUCT():

=SUMPRODUCT(--(MONTH(A2:A13)=2)*(B2:B13="x"))

For February:

enter image description here

You would use 3 for March, etc.

1
votes

Quite possible with COUNTIFS but the syntax must be observed:

=COUNTIFS(A4:A19,">="&F2,A4:A19,"<"&F3,B4:B19,"x")  

For convenience F1:F12 being loaded with Jan 16, Feb 16 etc.

0
votes
=SUM(IF((MONTH($A$2:$A$13)=MONTH(D2))*(DAY($A$2:$A$13)=DAY(D2))*($B$2:$B$13="x"),1,0))

type Ctrl+Shift+Enter

enter image description here