Count Unique combinations of 2 columns or more

C

Cheko

New Member
Joined
Apr 14, 2017
Messages
6
Hi guys, I´m new to the forum.

I´m having trouble with a formula that would allow me to search for the frequency of a column conditioned to anothe column, I can´t use pivot tables since the data comes from a Query from SQL that needs to be automated on excel.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Count Merchant[/TD]
[TD][/TD]
[TD]Merchant ID[/TD]
[TD]Team ID[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SAM[/TD]
[TD]=sum(if(frequency(if(....[/TD]
[TD][/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: right"]24168596915[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65"]SAM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DS[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: right"]21173871305[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65"][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65"]FBA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FBA[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: right"]24168596915[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: right"]15085426115
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65"]FBA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, align: right"]38275210615[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 61"]
<tbody>[TR]
[TD="class: xl65"]DS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help is appreciated!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
you are not stating clearly (for me anyway) what you want

is it how many of 24168596915 paired with sam and eg 123456789 paired with sam or DS
 
Upvote 0
Correct, I want to count of all the "merchant ID" paired with "ID" e.g. for the table above: SAM = 1(since it´s the same merchan ID#), DS =1 & FBA =1. The main problem is thar both Merchant ID & ID repeat througout the whole rows a vast number of times and i want the formula to count every unique pair only as one. I don´t know if I´m being clear?
 
Upvote 0
[TABLE="width: 821"]
<colgroup><col><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]number[/TD]
[TD]namenum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD="align: right"]24168596915[/TD]
[TD]sam24168596915[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ds[/TD]
[TD="align: right"]21173871305[/TD]
[TD]ds21173871305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fba[/TD]
[TD="align: right"]123456789[/TD]
[TD]fba123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD="align: right"]15085426115[/TD]
[TD]sam15085426115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fba[/TD]
[TD="align: right"]38275210615[/TD]
[TD]fba38275210615[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ds[/TD]
[TD="align: right"]24168596915[/TD]
[TD]ds24168596915[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ds[/TD]
[TD="align: right"]21173871305[/TD]
[TD]ds21173871305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD="align: right"]123456789[/TD]
[TD]sam123456789[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sam[/TD]
[TD="align: right"]15085426115[/TD]
[TD]sam15085426115[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ds[/TD]
[TD="align: right"]21173871305[/TD]
[TD]ds21173871305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of namenum[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]namenum[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ds21173871305[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ds24168596915[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fba123456789[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]fba38275210615[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sam123456789[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sam15085426115[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sam24168596915[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]a simple pivot table working on concatenated name_number ?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thannks for the reply, but I need to extract it as it is with a formula, as I said previously it can´t be pivot table or calculate a concatenated field and the another formula.
 
Upvote 0
Thanks for the reply again oldbrewer, not to sound rude but i know how to use pivot tables, the thing is that it´s a sheet that comes from an SQL Query from a metric job for an excel slide that gets sent automatically through an email nudge. And also it has to be presented into a specific format and a Pivot table doesn´t work. Thanks again!
 
Upvote 0
Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td][/td][td]
Count Merchant​
[/td][td][/td][td]
Merchant ID​
[/td][td]
Team ID​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
SAM​
[/td][td]
1​
[/td][td][/td][td]
24168596915​
[/td][td]
SAM​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
DS​
[/td][td]
1​
[/td][td][/td][td]
21173871305​
[/td][td]
FBA​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
FBA​
[/td][td]
2​
[/td][td][/td][td]
24168596915​
[/td][td]
SAM​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td][/td][td][/td][td][/td][td]
15085426115​
[/td][td]
FBA​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td][/td][td][/td][td][/td][td]
38275210615​
[/td][td]
DS​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in B2 copied down
=SUM(IF(FREQUENCY(IF($E$2:$E$100=A2,MATCH($D$2:$D$100,$D$2:$D$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
CountIFS and Sumif based on duplicate value in other columns
Replies
3
Views
391
jennybell
J
D
  • Solved
Count total award value per unique supplier
Replies
4
Views
127
Fluff
Fluff
T
  • Question Question
Count unique values based on two conditions
Replies
7
Views
739
Peter_SSs
Peter_SSs
P
  • Question Question
VBA CountIf function results in "0"
Replies
0
Views
1K
PlutoQX
P
P
  • Question Question
Combining two data tables in Sumifs Project Summary Report
Replies
1
Views
446
PaulyK
P

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top