Count if text and month/year matches

[yankiessg]

Hi,

I have a simple table below. But I couldn't figure out how to count the number of "Speaker" that was old in "Apr-15" .... Answer should be 4
And i could not do a pivot table with column label as "Month/Year", it just picks up multiples dates that falls under the same month. Sample excel as attached. Appreciate if anyone can help. Thank you


Mkt Date Month/Year
Speaker 4/1/2015 Apr-15
Speaker 1/1/2015 Jan-15
Speaker 4/30/2015 Apr-15
Speaker 4/15/2015 Apr-15
Speaker 1/17/2015 Jan-15
Speaker 1/9/2015 Jan-15
Speaker 3/10/2015 Mar-15
Speaker 3/19/2015 Mar-15
Speaker 1/30/2015 Jan-15
Speaker 5/14/2015 May-15
Speaker 6/10/2015 Jun-15
Speaker 4/11/2015 Apr-15
Speaker 6/22/2015 Jun-15
Speaker 7/11/2015 Jul-15


Rdgs
Rachel

[AlKey]

Please see attached file.

change Phone to Phones in F3

[Pete_UK]

Put this formula in G2:

=SUMPRODUCT(($A$2:$A$20="Speaker")*(TEXT($B$2:$B$20,"mmm-yy")=TEXT(G$1,"mmm-yy")))

and copy across to L2.

Hope this helps.

Pete

[Jonmo1]

Try this in G2 and filled down/right

=COUNTIFS($A$2:$A$20,$F2,$B$2:$B$20,">="&G$1,$B$2:$B$20,"<="&EOMONTH(G$1,0))

[Jonmo1]

Also,

If you change your formula in column C to
=TEXT(B2,"mmm-yy")

Then your pivot table will work as expected.