date - Excel Countif using month and letter as Criteria's

Question

I have a list of sales data that includes the date of the sale and also a sales type code. I'm trying to use COUNTIFS to count how many sales of a specific type there were during each month .

So for example:

Date.                  Sales Type Code
10/01/2014.                 S
12/01/2014.                 S
15/01/2014.                 O
18/01/2014.                 S
02/02/2014.                 O
08/02/2014.                 S

Would give me the following results:

Code S

January -       3    
February -      1

Code O

January -       1
February -      1

I have the formula

COUNTIFS('SALES LEDGER'!A:A,F2,'SALES LEDGER'!C:C,"<"&EOMONTH('MONTH Sales by Sales Type'!$C$1,0)+1)

Where A:A is the list of Sales Type Codes, F2 is the code I want to count, C:C is the list of dates and C1 is the first of the month I want to count (ie. 01/01/2014).

This works fine for January, giving me the expected result. But when I change C1 to 01/02/2014, it counts January and February together, rather than just February.

If anyone has any suggestions it would be appreciated!

Thanks

Answer 1

Your COUNTIFS formula specifies an end date but no start date, if you want to count for a specific month and year (based on C1) you need another criterion to specify the start date using C1, i.e.

=COUNTIFS('SALES LEDGER'!A:A,F2,'SALES LEDGER'!C:C,"<"&EOMONTH('MONTH Sales by Sales Type'!$C$1,0)+1,'SALES LEDGER'!C:C,">="&'MONTH Sales by Sales Type'!$C$1)