Count Unique Text Values

[Tikey]

I am trying to count unique text values and am using the following formula =SUM(IF(FREQUENCY(IF(AC1:AC16=1998,IF(AD1:AD16<>"",MATCH("~"&AD1:AD16,AD1:AD16&"",0))),ROW(AD1:AD16)-ROW(AD1)+1),1)) entered using cntrl, shft, enter.

My date cells are AC1:AC16 and the formula works fine with only the year in these cells, but these cells contain the year in the format 01/01/2018, is there a way of achieving this. Also is it possible for the year to be a range ie 1998 & 1999.

Thanks

 

[AhoyNC]

To just get the year from column AC try:
=SUM(IF(FREQUENCY(IF(YEAR(AC1:AC16)=1998,IF(AD1:AD16<>"",MATCH("~"&AD1:AD16,AD1:AD16&"",0))),ROW(AD1:AD16)-ROW(AD1)+1),1))

For a range try:

[TABLE="width: 1103"]
<colgroup><col width="1103"></colgroup><tbody>[TR]
   [TD="width: 1103"]=SUM(IF(FREQUENCY([COLOR=#ff0000]IF(YEAR(AC1:AC16)>=1998,IF(YEAR(AC1:AC16)<=1999[/COLOR],IF(AD1:AD16<>"",MATCH("~"&AD1:AD16,AD1:AD16&"",0)))),ROW(AD1:AD16)-ROW(AD1)+1),1))
[/TD]
 [/TR]
</tbody>[/TABLE]

Enter with CTRL-SHIFT-ENTER

 

[Tikey]

Hi

Both formulas work great, thanks very much.

 

[AhoyNC]

You're welcome. Thanks for the feedback.