Use only Month and Year from date as criteria in countifs?

Question

I have an excel countifs formula with multiple criteria:

=COUNTIFS('Opp Data'!$E:$E,Home!$B11,'Opp Data'!$B:$B,Home!$D11)

My dates appear in column B on my Sheet OPP Data like so:

28/03/2016
21/04/2016

My lookup value in D11, D12 etc, will appear like so:

March - 2016
April - 2016

The dates will always be formatted like this.

Is there a way i can amend my countifs formula so that it recognises the month and the year as shown above from the dates in column B on my Opp Data sheet?

I have tried to get started by working on something like the below, but this doesn't work:

=COUNTIFS('Opp Data'!$E:$E,Home!$B11,TEXT('Opp Data'!$B:$B,"mmmm"),Home!$D11)

Please could someone show me where i am going wrong?

Thanks in advance

It isn't clear from your question - will the dates always be formatted the same way in D11? Because right now you have 2 different methods of formatting. The more consistent your data, the easier you can manipulate it. — Grade 'Eh' Bacon
@Grade'Eh'Bacon thanks for your comment, please see updated question. Yes lookup dates will always have same formatting. — G_Man97
if you can adjust your lookup months to be numbers, it will be easier. So 1 for Jan, 2 for Feb ... Then you can write something like this: =COUNTIFS('Opp Data'!$E:$E,Home!$B11,Month('Opp Data'!$B:$B),Home!$D11) — Scott Holtzman
@ScottHoltzman thanks for this suggestion, however this would not allow me to use the year, this only works if going of month number. The problem is i have dates dating back from 2012 to 2016. — G_Man97
@Trashman Your comment is incorrect; depending on your language / region settings, Excel will recognize dd/mm/yyyy as a date. — Grade 'Eh' Bacon

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-03-23T20:40:40

If you are willing to make your lookup table actual dates, you can use the SUMPRODUCT formula to get what you are after.

I have demonstrated in the below screenshot

Formula in F1: =SUMPRODUCT(--($A$1:$A$4=D1)*--(YEAR($B$1:$B$4)=YEAR(E1))*--(MONTH($B$1:$B$4)=MONTH(E1)))

If you really want to keep the text format you have, the following formula will work as in below screenshot:

Formula in I1: =SUMPRODUCT(--($A$1:$A$4=D1)*--(TEXT($B$1:$B$4,"YYYY")=RIGHT(H1,4))*--(TEXT($B$1:$B$4,"mmmm")=LEFT(H1,FIND("-",H1)-2)))

Use only Month and Year from date as criteria in countifs?

1 Answers