How to count unique text values in a range, with some constraints given?

user_11381490 0
Viewed 62

For example, I have a range cells:

Date        Number
04/1/2019     A1
07/1/2019     A2
14/1/2019     -
15/1/2019     A1
02/2/2019     A3
07/2/2019     A1

and I want to count the amount of unique number in January, which is 2. What should I do? I am using anolder version of Excel, whcich does not support functions such as FILTER() and UNIQUE(). I have found a formula online:

{=SUM(IF(FREQUENCY(IF(data<>"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1))}

which can count unique text values in a range which ignoring blank cells. However, I was unable to modify it to suit my usage, since it doesn't allow me to insert any criteria (such as date and ignore "-"). Most of the solution online involves functions such as FILTER() and UNIQUE(), which I am not able to use.

excel-formula excel
1 Answers

enter image description here

Slight modification of your attempt in D1, based on this handy website:

=SUM(--(FREQUENCY(IF((B2:B7<>"-")*(MONTH(A2:A7)=1),MATCH(B2:B7,B2:B7,0)),ROW(B2:B7)-ROW(B2)+1)>0))

Entered as array formula through CtrlShiftEnter

This boolean logic allows you to enter many more criteria if needed, such as YEAR if your dates can span more than the year 2019 and you need to filter on that too. Or include a check against empty values. It's very versatile.

user_97581940
Related