Count unique values based on two conditions

[thomasuponor]

I am looking for a way to count unique values in column E based on column M = "Installer/contractor" AND column O = {"A1";"A2";"A3";"B1"B2"}

Currently my formula is: (it only includes column M)

=SUM(--(FREQUENCY(IF('Visit Data'!$M:$M="Installer/Contractor";MATCH('Visit Data'!$E:$E;'Visit Data'!$E:$E;0));ROW('Visit Data'!$E:$E)-ROW('Visit Data'!$E$3)+1)>0))

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[DanteAmor]

How about:

=SUM(--(FREQUENCY(IF(('Visit Data'!$M3:$M200="Installer/Contractor")*(ISNUMBER(SEARCH("|" &'Visit Data'!O3:O200&"|","|A1|A2|A3|B1|B2|"))),MATCH('Visit Data'!$E3:$E200,'Visit Data'!$E3:$E200,0)),ROW('Visit Data'!$E3:$E200)-ROW('Visit Data'!$E$3)+1)>0))

I suggest you limit the range of cells, because it is an array formula and consumes many resources.

 

[thomasuponor]

How about:

=SUM(--(FREQUENCY(IF(('Visit Data'!$M3:$M200="Installer/Contractor")*(ISNUMBER(SEARCH("|" &'Visit Data'!O3:O200&"|","|A1|A2|A3|B1|B2|"))),MATCH('Visit Data'!$E3:$E200,'Visit Data'!$E3:$E200,0)),ROW('Visit Data'!$E3:$E200)-ROW('Visit Data'!$E$3)+1)>0))

I suggest you limit the range of cells, because it is an array formula and consumes many resources.

This is really cool. Works without problems. Thanks!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

@thomasuponor
It is a pity that didn't happen as this much shorter formula works for me and would for you too if you had an appropriate Excel version. Without knowing your version, I don't know if this post is a waste of time or not.

=ROWS(UNIQUE(FILTER('Visit Data'!E3:E200,('Visit Data'!M3:M200="Installer/Contractor")*(ISNUMBER(SEARCH("|" &'Visit Data'!O3:O200&"|","|A1|A2|A3|B1|B2|"))))))

 

[thomasuponor]

@thomasuponor
It is a pity that didn't happen as this much shorter formula works for me and would for you too if you had an appropriate Excel version. Without knowing your version, I don't know if this post is a waste of time or not.

=ROWS(UNIQUE(FILTER('Visit Data'!E3:E200,('Visit Data'!M3:M200="Installer/Contractor")*(ISNUMBER(SEARCH("|" &'Visit Data'!O3:O200&"|","|A1|A2|A3|B1|B2|"))))))

@Peter_SSs Let me try this one again. And let me also update my Excel information (Y) cheers and best

 

[Peter_SSs]

@Peter_SSs Let me try this one again. And let me also update my Excel information (Y) cheers and best

Cheers. Interested to see how this goes for you if you have Excel 365 with the FILTER & UNIQUE functions.
In any case looking forward to your Excel version info in your profile.
BTW, you need a lower case y to get