Count Unique combinations of 2 columns or more

[Cheko]

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.

	Count Merchant		Merchant ID	Team ID
SAM	=sum(if(frequency(if(....		24168596915 <tbody> </tbody>	SAM <tbody> </tbody>
DS			21173871305 <tbody> </tbody>	FBA <tbody> </tbody> <tbody> </tbody>
FBA			24168596915 <tbody> </tbody>	SAM
			15085426115 <tbody> </tbody>	FBA <tbody> </tbody>
			38275210615 <tbody> </tbody>	DS <tbody> </tbody>

<tbody>
</tbody>

Any help is appreciated!

 

[oldbrewer]

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

 

[Cheko]

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?

 

[oldbrewer]

name	number	namenum
sam	24168596915	sam24168596915
ds	21173871305	ds21173871305
fba	123456789	fba123456789
sam	15085426115	sam15085426115
fba	38275210615	fba38275210615
ds	24168596915	ds24168596915
ds	21173871305	ds21173871305
sam	123456789	sam123456789
sam	15085426115	sam15085426115
ds	21173871305	ds21173871305
					Count of namenum
					namenum	Total
					ds21173871305	2
					ds24168596915	1
					fba123456789	1
					fba38275210615	1
					sam123456789	1
					sam15085426115	2
					sam24168596915	1
					Grand Total	9
		a simple pivot table working on concatenated name_number ?

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

 

[Cheko]

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.

 

[oldbrewer]

why cannot a pivot table work on whatever data comes in ?

 

[Cheko]

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!

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​
1​		Count Merchant​		Merchant ID​	Team ID​
2​	SAM​	1​		24168596915​	SAM​
3​	DS​	1​		21173871305​	FBA​
4​	FBA​	2​		24168596915​	SAM​
5​				15085426115​	FBA​
6​				38275210615​	DS​
7​

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.