Count if sold multiple products to the same account

[IvanYerk]

I envision a summary as such:

Seller	GAP	MBP	BOTH
John
Veronica
Diana
Kevin

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

Raw data is as follows:

Seller	Account number	Product
Kevin	123	GAP
Veronica	456	GAP
Veronica	456	MBP
Kevin	789	MBP
John	987	GAP
Veronica	654	MBP
John	321	GAP
Diana	657	MBP

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I want it to count products sold by each seller (and account) with a wrinkle.

For example, Veronica would have a count of 1 in the MBP column and 1 in the BOTH column based on the above data because she sold BOTH products to one account (456) and only one product to the other account (654).

Hope this is clear. This board is immensely helpful. Thanks in advance!!

 

[Marcelo Branco]

Questions
Could you tell us the expected results for each seller considering the data sample in post 1?

What are the expected results for Veronica considering the two scenarios below?

SCENARIO 1 ​
Seller​	Account number​	Product​
Kevin​	123​	GAP ​
Veronica​	456​	GAP​
Kevin​	789​	MBP​
John​	987​	GAP​
Veronica​	654​	MBP​
John​	321​	GAP​
Diana​	657​	MBP​
SCENARIO 2 ​
Seller​	Account number​	Product​
Kevin​	123​	GAP ​
Veronica​	456​	GAP​
Veronica​	456​	MBP​
Kevin​	789​	MBP ​
John​	987​	GAP​
Veronica​	654​	GAP​
Veronica​	654​	MBP​
John​	321​	GAP​
Diana​	657​	MBP​

<tbody>
</tbody>

M.

 

[IvanYerk]

SCENARIO 1
Seller	GAP	MBP	BOTH
John	2	0	0
Veronica	1	1	0
Diana	0	1	0
Kevin	1	1	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

SCENARIO 2
Seller	GAP	MBP	BOTH
John	2	0	0
Veronica	0	0	2
Diana	0	1	0
Kevin	1	1	0

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

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Seller​	GAP​	MBP​	BOTH​		Seller​	Account number​	Product​
2​	John​	2​	0​	0​		Kevin​	123​	GAP​
3​	Veronica​	0​	1​	1​		Veronica​	456​	GAP​
4​	Diana​	0​	1​	0​		Veronica​	456​	MBP​
5​	Kevin​	1​	1​	0​		Kevin​	789​	MBP​
6​						John​	987​	GAP​
7​						Veronica​	654​	MBP​
8​						John​	321​	GAP​
9​						Diana​	657​	MBP​
10​

Array formula in B2 copied down
=SUM(IF($F$2:$F$100=$A2,IF(ISNA(MATCH($G$2:$G$100,IF($F$2:$F$100=$A2,IF($H$2:$H$100=C$1,$G$2:$G$100)),0)),IF($H$2:$H$100=B$1,1))))
Ctrl+Shift+Enter, not just Enter

Array formula in C2 copied down
=SUM(IF($F$2:$F$100=$A2,IF(ISNA(MATCH($G$2:$G$100,IF($F$2:$F$100=$A2,IF($H$2:$H$100=B$1,$G$2:$G$100)),0)),IF($H$2:$H$100=C$1,1))))
Ctrl+Shift+Enter, not just Enter

Array formula in D2 copied down
=SUM(COUNTIFS($F$2:$F$100,$A2,$H$2:$H$100,C$1,$G$2:$G$100,IF($F$2:$F$100=$A2,IF($H$2:$H$100=B$1,$G$2:$G$100))))
Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[IvanYerk]

Works perfectly. Arrays are my kryptonite, so I greatly appreciate your help!!

 

[Marcelo Branco]

You're welcome. Glad to help

M.