Formula to return unique values based on 4 criteria

[thomasuponor]

Hello everybody,

I want to count unique value with following formula, which usually works, but does not for this task. The extra add on is Data'!$AA:$AA;"*Dahl*" (I believe this is the reason it's not working)

=SUM(--(FREQUENCY(IF(('Visit Data'!$AD:$AD="Distributor")*(ISNUMBER(SEARCH('Visit Data'!$AG:$AG;"Visit")))*(ISNUMBER(SEARCH('Visit Data'!$V:$V;"Kim")))*(ISNUMBER(SEARCH('Visit Data'!$AA:$AA;"*Dahl*")));MATCH('Visit Data'!$AA:$AA;'Visit Data'!$AA:$AA;0));ROW('Visit Data'!$AA:$AA)-ROW('Visit Data'!$AA$3)+1)>0))

Anyone able to provide help here?
Thx

 

[DanteAmor]

Here another option. In this type of array formulas I recommend not using the entire column, delimit the range of cells.

=SUMPRODUCT(--('Visit Data'!$AD$2:$AD$13="Distributor"),
--(ISNUMBER(SEARCH("Visit",'Visit Data'!$AG$2:$AG$13))),
--(ISNUMBER(SEARCH("kim",'Visit Data'!$V$2:$V$13))),
--(ISNUMBER(SEARCH("dahl",'Visit Data'!$AA$2:$AA$13))),
--(MATCH('Visit Data'!$AA$2:$AA$13,'Visit Data'!$AA$2:$AA$13,0)=ROW('Visit Data'!$AD$2:$AD$13)-1))

 

[thomasuponor]

correction:

=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH("Visit";'Visit Data'!$AG:$AG)*SEARCH("Kim";'Visit Data'!$V:$V)*SEARCH("Dahl";'Visit Data'!$AA:$AA)/('Visit Data'!$AD:$AD="Distributor"));MATCH('Visit Data'!$AA:$AA;'Visit Data'!$AA:$AA;0));ROW('Visit Data'!$AA:$AA))>0))
confirmed with CTRL + SHIFT + ENTER

This totally works (Y)

 

[thomasuponor]

It looks like what @DonkeyOte posted works fine. I still don't understand why the non-array formula I suggested won't work, unless the commas were left unchanged. It also appears that you want only instances where column AD contains "Distributor" rather than that string found anywhere, so a revised version would be:
=COUNTIFS('Visit Data'!$AD:$AD;"Distributor";'Visit Data'!$AG:$AG;"*Visit*";'Visit Data'!$V:$V;"*Kim*";'Visit Data'!$AA:$AA;"*Dahl*")

Again, the countifs does not create a result of unique values. If your conditions are multiple times on the list then it will count them each time

 

[KRice]

Hi @DonkeyOte and @thomasuponor, thanks for the explanations. Sorry, but I completely missed the uniqueness requirement applied to column AA. I'm glad you got it working.

 

[Rahul1987]

correction:

=SUM(--(FREQUENCY(IF(ISNUMBER(SEARCH("Visit";'Visit Data'!$AG:$AG)*SEARCH("Kim";'Visit Data'!$V:$V)*SEARCH("Dahl";'Visit Data'!$AA:$AA)/('Visit Data'!$AD:$AD="Distributor"));MATCH('Visit Data'!$AA:$AA;'Visit Data'!$AA:$AA;0));ROW('Visit Data'!$AA:$AA))>0))
confirmed with CTRL + SHIFT + ENTER

Hi Donkey ote,
Why is that semi colon? And why visit Data is repetitive when everything is in one tab?

 

[Tom.Jones]

@Rahul1987,

Why is that semi colon? And why visit Data is repetitive when everything is in one tab?

Depending on the regional settings, a formula can use, as a separator, a comma or semicolon.