# Count if sold multiple products to the same account

#### IvanYerk

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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Marcelo Branco

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

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

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

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

M.

Replies
3
Views
533

1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

### 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.

### Which adblocker are you using?

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