0

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

G_Man97
  • 37
  • 2
  • 7
  • 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 Mar 23 '16 at 20:17
  • @Grade'Eh'Bacon thanks for your comment, please see updated question. Yes lookup dates will always have same formatting. – G_Man97 Mar 23 '16 at 20:19
  • 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 Mar 23 '16 at 20:19
  • @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 Mar 23 '16 at 20:24
  • @G_Man97 - then make it an actual date ... so `1/1/2012, 2/1/2012 ... 11/1/2014 ... 12/31/2015` Then you can write something like `=COUNTIFS('Opp Data'!$E:$E,Home!$B11,Month('Opp Data'!$B:$B),Month(Home!$D11),Year('Opp Data!$B:$B),Year(Home!$D11)` – Scott Holtzman Mar 23 '16 at 20:26
  • @G_Man97 - actually that exact formula won't work, but having the dates will allow you use the `Month` and `Year` functions against your data will with more ease than anything else. – Scott Holtzman Mar 23 '16 at 20:32
  • I think your problem may be how the dates were entered. If you type in 28/03/2016 Excel does not even recognize it as a date, so TEXT(), month() and day() will not even treat the data right. Excel has to recognize it as a date and store it as a datenumber. Try entering your dates in the "American" way - mm/dd/yyyy and formatting that column in the way that makes more sense, dd/mm/yyyy - then text(), month(), and/or year() functions should work. – Trashman Mar 23 '16 at 20:35
  • 1
    @Trashman Your comment is incorrect; depending on your language / region settings, Excel will recognize dd/mm/yyyy as a date. – Grade 'Eh' Bacon Mar 23 '16 at 20:38

1 Answers1

0

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)))

enter image description here

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)))

enter image description here

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • 1
    Another way to keep his original text is to use DATEVALUE() around it, then insert into the sumproduct formula. =SUMPRODUCT(--($A$1:$A$4=D1)*--(YEAR($B$1:$B$4)=YEAR(DATEVALUE(H1)))*--(MONTH($B$1:$B$4)=MONTH(DATEVALUE(H1)))) – Trashman Mar 23 '16 at 21:04