COUNTIF occurrences between two dates

[bgrice]

Hello, everyone. This is my first post so apologies if I am breaking etiquette - I mean not to and please point out if I am. I am hoping someone can help with the following:

I need a formula for the table below that will count the number of times a specified fruit is mentioned in a particular month. I have tried COUNTIF and COUNTIFS but with no luck and I have run out of ideas. So, for example, if I want it to calculate how many times 'apples' appears in the month of May, which is twice, I have been trying the following formula. Grateful for your help. Thanks, Benno.

=COUNTIFS(A:A,">=01/05/2017",A:A,"<=31/05/2017,B:B, "*apples*")
[TABLE="class: outer_border, width: 250"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Fruit
[/TD]
[/TR]
[TR]
[TD]01/05/17
[/TD]
[TD]apples, oranges
[/TD]
[/TR]
[TR]
[TD]06/05/17
[/TD]
[TD]apples, pears
[/TD]
[/TR]
[TR]
[TD]20/06/17
[/TD]
[TD]pears, bananas
[/TD]
[/TR]
[TR]
[TD]08/08/17
[/TD]
[TD]oranges, apples
[/TD]
[/TR]
[TR]
[TD]11/08/17
[/TD]
[TD]bananas, oranges
[/TD]
[/TR]
[TR]
[TD]15/10/17
[/TD]
[TD]grapes, apples
[/TD]
[/TR]
[TR]
[TD]31/11/17
[/TD]
[TD]pears, grapes
[/TD]
[/TR]
[TR]
[TD]20/12/17
[/TD]
[TD]bananas, oranges
[/TD]
[/TR]
[TR]
[TD]03/01/18
[/TD]
[TD]bananas, pears
[/TD]
[/TR]
[TR]
[TD]11/01/18
[/TD]
[TD]pears, apples
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jproffer]

This is an example. Don't copy my formula or data directly as I had to change the date formats...

Excel 2013/2016

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]5/1/2017[/TD]
[TD="align: center"]apples, oranges[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]5/6/2017[/TD]
[TD="align: center"]apples, pears[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]6/20/2017[/TD]
[TD="align: center"]pears, bananas[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]8/8/2017[/TD]
[TD="align: center"]oranges, apples[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]8/11/2017[/TD]
[TD="align: center"]bananas, oranges[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]10/15/2017[/TD]
[TD="align: center"]grapes, apples[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]11-31-17[/TD]
[TD="align: center"]pears, grapes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]12/20/2017[/TD]
[TD="align: center"]bananas, oranges[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]1/3/2018[/TD]
[TD="align: center"]bananas, pears[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]2/11/2018[/TD]
[TD="align: center"]pears, apples[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=COUNTIFS(A:A,">=5/01/2017",A:A,"<=5/31/2017",B:B, "*apples*")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



But assuming that's the result you want (2), then:

=COUNTIFS(A:A,">=01/05/2017",A:A,"<=31/05/2017",B:B, "*apples*")

try that .

You were missing a closing parenthesis.