Count Uniques, If Criteria Is Met

[peterhinton]

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

BRANCH	PARTNER	SWITZERLAND
1200	TRU	SWITZERLAND
1200	TRU	SWITZERLAND
1201	TRU	SWITZERLAND
1201	TRU	SWITZERLAND
1202	TRU	SWITZERLAND
1451	TRU	FRANCE
1451	TRU	FRANCE
1451	TRU	FRANCE
1451	TRU	FRANCE
1451	TRU	FRANCE
1452	TRU	FRANCE
1452	TRU	FRANCE
1706	TOY	SPAIN
1706	TRU	SWITZERLAND
1706	TRU	SWITZERLAND

<tbody>
</tbody><colgroup><col span="2"><col></colgroup>

 

[Marcelo Branco]

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]

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]

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]

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]

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

1529240984	E1​	M1313100006	3D​	11​
1515942722	E1​		3D​	2N​
1531700240	E1​	M1300600167	3D​	11​
1246345712	E1​		3D​	11​
1514862997	E1​	M1312100119	3D​	11​
1503119907	E1​	M1311400034	3D​	11​
1522488433	E1​	M13101EX101	3D​	11​
1548153332	E1​		3D​	11​
1534934668	E1​	M1331100075	3D​	11​
1539218952	E1​	M1311500096	3D​	11​
1513904670	E1​	M13201EX678	3D​	11​
1518058122	E1​	M1313100043	3D​	11​
1527669789	E1​	M1320100819	3D​	11​

<tbody> </tbody>

And my formula was:

{=SUM(IF(FREQUENCY(IF(B1:B13="E1",IF(D1:D13="3D",IF(ISNUMBER(MATCH(E1:E13,{"11";"2C";"2N";"B1";"C7";"CF";"KM"},0)),MATCH(C1:C13,C1:C13,0)))),ROW(C1:C13)-ROW(C1)+1),1))}
Thanks again for your help on this!

 

[Marcelo Branco]

Re: Count Uniques, If Criteria Is Met - HARD

Try

=SUM(IF(FREQUENCY(IF(B1:B13="E1",IF(D1:D13="3D",IF(ISNUMBER(MATCH(E1:E13,{"11";"2C";"2N";"B1";"C7";"CF";"KM"},0)),IF(C1:C13<>"",MATCH(C1:C13,C1:C13,0))))),ROW(C1:C13)-ROW(C1)+1),1))


or (without quotes)

=SUM(IF(FREQUENCY(IF(B1:B13="E1",IF(D1:D13="3D",IF(ISNUMBER(MATCH(E1:E13,{11;"2C";"2N";"B1";"C7";"CF";"KM"},0)),IF(C1:C13<>"",MATCH(C1:C13,C1:C13,0))))),ROW(C1:C13)-ROW(C1)+1),1))
Ctrl+Shift+Enter

M.

 

[cgebke]

Re: Count Uniques, If Criteria Is Met - HARD

And you’re a GENIUS. Thanks for all the help. I am onlyhalfway through this massive project and might reach out to you again. I knowyou spent a lot of your personal time working on this and it is greatly appreciated.

 

[Marcelo Branco]

Re: Count Uniques, If Criteria Is Met - HARD

You're welcome. Thanks for the feedback.

M.