Count Uniques, If Criteria Is Met

peterhinton

Active Member
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>

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

CROY1985

Active Member
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

MrExcel MVP
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

Active Member
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

Active Member
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

Active Member
Re: Count Uniques, If Criteria Is Met - HARD

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

Tetra201

MrExcel MVP
Re: Count Uniques, If Criteria Is Met - HARD

You are most welcome.

cgebke

New Member
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

MrExcel MVP
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

New Member
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))

Replies
1
Views
567
Replies
5
Views
653
Replies
19
Views
586
Replies
12
Views
659
Replies
3
Views
1K

1,190,691
Messages
5,982,314
Members
439,772
Latest member
KimPhoenixT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back