Count Uniques, If Criteria Is Met

peterhinton · Dec 9, 2016

how would I count the number of unique branch numbers in the below table, that have the partner TRU, and Country SWITZERLAND ?

The desired answer would be 4

or TRU and FRANCE would return 2

Data Starts in A1
[TABLE="width: 223"]
<tbody>[TR]
[TD]BRANCH[/TD]
[TD]PARTNER[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1200[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1200[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1201[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1201[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1202[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1451[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1451[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1451[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1451[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1451[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1452[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1452[/TD]
[TD]TRU[/TD]
[TD]FRANCE[/TD]
[/TR]
[TR]
[TD]1706[/TD]
[TD]TOY[/TD]
[TD]SPAIN[/TD]
[/TR]
[TR]
[TD]1706[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
[TR]
[TD]1706[/TD]
[TD]TRU[/TD]
[TD]SWITZERLAND[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]

Marcelo Branco · Mar 8, 2019

Re: Count Uniques, If Criteria Is Met - HARD

Avoid references to entire columns, like G:G, W:W, AS:AS, in array formulas. Tell us the exact location of your data (columns and rows).

M.

cgebke · Mar 14, 2019

Re: Count Uniques, If Criteria Is Met - HARD

Thank you for your help and on this and sorry for the delay. Thisproblem is crushing me. This is the formula I am using. If I hitCtrl+Shift+Enter, I get an answer of 0. If I just hit enter I get an answer of1. Unfortunately both are definitely wrong. I’ve adjusted the row length as yousuggested, but unfortunately I have a lot of data. Everything in the formulabelow is in the actual columns. “E1” is in column G, “3D” is in column W, allthe weird variables are in column AS, and I am trying to find the unique valuesin column L. The fact that the below even provides an answer is an improvement andI am grateful. Anything you can do to help would be appreciated.

=SUM(IF(FREQUENCY(IF(G2:G40000="E1",IF(W2:W40000="3D",IF(ISNUMBER(MATCH(AS2:AS40000,{"11";"2C";"2N";"B1";"C7";"CF";"KM"},0)),MATCH(L2:L40000,L2:L40000,0)))),ROW(L2:L40000)-ROW(L2)+1),1))

Marcelo Branco · Mar 14, 2019

Re: Count Uniques, If Criteria Is Met - HARD

Your formula seems perfect. It worked for me with a very small data sample (confirmed with Ctrl+Shift+Enter).
I don't know why is not working for you.

M.

Marcelo Branco · Mar 14, 2019

Re: Count Uniques, If Criteria Is Met - HARD

A possible issue:
Are the values 11 in column AS numbers or text? Check.

If they are numbers they don't match with {"11";"2C";"2N";"B1";"C7";"CF";"KM"}
In this case you should use {11;"2C";"2N";"B1";"C7";"CF";"KM"} without quotes around 11.

On the other hand, if they are texts you should use
{"11";"2C";"2N";"B1";"C7";"CF";"KM"}

M.

cgebke · Mar 19, 2019

Re: Count Uniques, If Criteria Is Met - HARD

First off, thank you so much for sticking with me throughthis extremely frustrating endeavor. The numbers are listed as text, so the “11”should be OK. I’ve also tried using the formula on a portion of the data setand am getting an error “Value not available”
To make it simple, I made my data set:
A B C D E

[TABLE="width: 272"]
<tbody>[TR]
[TD="width: 74, bgcolor: transparent"] 1529240984
[/TD]
[TD="width: 64, bgcolor: transparent"]

E1

[/TD]
[TD="width: 107, bgcolor: transparent"] M1313100006
[/TD]
[TD="width: 64, bgcolor: transparent"]

3D