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>

 

[CROY1985]

Re: Count Uniques, If Criteria Is Met - HARD

I have a solution....its not pretty, but its a starting point:

It requires 2 helper columns, the 1st one concatenates all 3 columns together:

So in cell D2 i have: =a2&B2&c2

Next helper column identifies the ones within that which are unique.

In Cell E2: =IF(COUNTIFS(D$2:D2,D2)>1,0,1)

Finally, the formula to return unique branch numbers:

=sumifs(E:E,B:B,"Tru",C:C,"Switzerland")



....i have a feeling there will be a way to merge my 2 helper columns into 1 array formula, but alas, i've not put enough coffee in for that.

 

[Tetra201]

Re: Count Uniques, If Criteria Is Met - HARD

Is this what you need (to be entered using Ctrl+Shift+Enter)?

=SUM(IF(FREQUENCY(IF((B2:B16="TRU")*(C2:C16="SWITZERLAND"),A2:A16),IF((B2:B16="TRU")*(C2:C16="SWITZERLAND"),A2:A16))>0,1))

 

[CROY1985]

Re: Count Uniques, If Criteria Is Met - HARD

Is this what you need (to be entered using Ctrl+Shift+Enter)?

=SUM(IF(FREQUENCY(IF((B2:B16="TRU")*(C2:C16="SWITZERLAND"),A2:A16),IF((B2:B16="TRU")*(C2:C16="SWITZERLAND"),A2:A16))>0,1))

And there's the array formula i mentioned! ignore my answer.

 

[peterhinton]

Re: Count Uniques, If Criteria Is Met - HARD

Tetra, that works beautifully for what I need, Thank You,

Thanks for the effort CROY, I should have mentioned I didn't really want help columns as my sheet is already reaching 50mb in size,
I had thought of using a helper to concatenate then using the formula : {=SUM(1/COUNTIF(A2:A100000,A2:A100000))} to count the unique values,

 

[peterhinton]

Re: Count Uniques, If Criteria Is Met - HARD

AND IT IGNORES BLANKS
Making it that little bit more robust !

 

[Tetra201]

Re: Count Uniques, If Criteria Is Met - HARD

You are most welcome.

 

[cgebke]

Re: Count Uniques, If Criteria Is Met - HARD

I am having a very similar problem, but can’t seem to getthe formula to work. My table looks like the below, but includes a lot morevariables. I tried to simplify it. I wanted to use the same column names as inmy sheet so they are not sequential (Columns are G, L, W, AS).

G L W AS
--------------------------------------
E1 B26 Frog 2K
E5 B56 Fish 6T
E2 C62 Bat 3F
E1 B26 Frog 1G
E6 1Q5 Cat 2Q
E1 F89 Frog 9U
E1 S15 Dog 5D



I am trying to write a formula that counts thenumber of unique values in Column L, but only if G=”E1”, W=”Frog”, and AS = “2K”,or “1G”, or “9U”. The answer should be 2.
I was able to write a formula that could do thisand count blanks in Column L (pasted below) and was hoping to change thatslightly to count unique numbers but had no luck.

=SUM(COUNTIFS($G:$G,"E1",$L:$L,"",$W:$W,"Frog",$AS:$AS,{"2K","1G","9U"}))


Any help would be greatly appreciated.

 

[Marcelo Branco]

Re: Count Uniques, If Criteria Is Met - HARD

cgebke

Try this array formula (adjust the ranges)
=SUM(IF(FREQUENCY(IF(G2:G100="E1",IF(W2:W100="Frog",IF(ISNUMBER(MATCH(AS2:AS100,{"2K";"1G";"9U"},0)),MATCH(L2:L100,L2:L100,0)))),ROW(L2:L100)-ROW(L2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[cgebke]

Re: Count Uniques, If Criteria Is Met - HARD

Thanks for the quick reply! I tried entering the formula using my actual variables (not the ones in my example). See below. Unfortunately the result is 0, which is not accurate. Do you see any issues with how I adjusted the formula? I will freely admit I don't fully understand it and could have done something wrong.

=SUM(IF(FREQUENCY(IF(G:G="E1",IF(W:W="3D",IF(ISNUMBER(MATCH(AS:AS,{"11";"2C";"2N";"B1";"C7";"CF";"KM"},0)),MATCH(L:L,L:L,0)))),ROW(L:L)-ROW(L2)+1),1))