Counting with multiple conditions across columns

Question

I am trying to work out how to count over two columns with conditions including a date range rather than text or value.

Column A dates (written in dd,mm,yyyy format), Column B text (apples, pears, bananas etc.)

I would like to count, how many apples were bought between 1st & 31st Jan. I first needed to work out how many entries there were for each month:

=Sumproduct((A1:A100>=date(2003,1,1))*(A1:A100<=date(2003,1,31) and so on. But I think there must be an easier formula for that?

I can't work out how I would count how many apples were sold in January, pears in Feb etc.

If you use =text(a2, "dd-mmm-yyyy") in a cell do you get a transformed date? Is there a text column header label in A1? — user4039065

maxhob17 maxhob17 · Accepted Answer · 2017-07-11T14:31:37

I'm not sure if this is necessarily a simpler formula, but maybe more scalable as you could add further conditions?

=COUNTIFS(A1:A100,">="&DATE(2003,1,1),A1:A100,"<"&DATE(2003,2,1), B1:B100, "apples")

To get the number of apples in January.