Excel - Get unique count based on multiple columns (including date)

0
votes

I'm trying to get a unique count of data in Column B that fall into the month of June (Column A date field)

Screenshot of Spreadsheet

I highlighted the rows that fall within June in Orange and the duplicate data in red to make it easier to view.

Count Total formula is a simple:

=COUNTA(A:A)-1

Unique Data formula is:

=SUMPRODUCT(1/COUNTIF(B2:B21,B2:B21))

Count June formula is:

=COUNTIFS(A:A,">=01/06/2020",A:A,"<30/6/2020")

But I can't figure out how get a count of unique data that falls within June (expected result is 13)

I've tried filter/unique formulas based on Excel - Count unique values that meets multiple criteria

But I just can't get it to work. I know I could do it with VBA but this is part of a larger spreadsheet and every other part of the spreadsheet I've been able to do with Formulas, so would like to be able to do this last part with formulas too.

Anyone can help will be a life saver, it's been driving me nuts for the last couple hours.

1
excelexcel-formula
What version of Excel? – Ron Rosenfeld
windows based excel 2016 – Michael Liew

1 Answers

1
votes

In Excel 2016, which does not have the UNIQUE or FILTER functions, you can use this somewhat convoluted formula for a Unique count of June entries:

=SUM(IF(FREQUENCY(IF(LEN(IF(MONTH(Table1[Date])=6,Table1[Data],""))>0,MATCH(IF(MONTH(Table1[Date])=6,Table1[Data],""),IF(MONTH(Table1[Date])=6,Table1[Data],""),0),""),IF(LEN(IF(MONTH(Table1[Date])=6,Table1[Data],""))>0,MATCH(IF(MONTH(Table1[Date])=6,Table1[Data],""),IF(MONTH(Table1[Date])=6,Table1[Data],""),0),""))>0,1))
  • This part of the formula: IF(MONTH(Table1[Date])=6,Table1[Data],"") returns an array consisting of all of the June Data entries.
  • The LEN(... eliminates the resultant blanks
  • The Frequency function will then have us wind up with a count of 1 for each entry.
  • Then we just add it up.

Note that I used a Table and structured references, but you can convert it to regular addressing if you need to.

Of course, if you had Excel O365, you could use the simpler:

=COUNTA(UNIQUE(FILTER(Table1[Data],MONTH(Table1[Date])=6)))

enter image description here