Group number of purchased SKUs by counting customer

B

blader1989

New Member
Joined
May 6, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm building a report that will group the number of purchased SKUs by counting customer with total sales > 0, I have tried pivot and many ways but I could not get the result as my expectation in attached file so I hope you can help me.

Thank you for your reading time, your help will be most appreciated!!!

Here is my table example
Product NameCustomer CodeBilling DateQtyYearMonthTotal Sales
Product 45
121001076​
08/02/2023​
5​
2023​
2​
864285​
Product 46
121001076​
04/01/2023​
10​
2023​
1​
2033330​
Product 46
121001076​
01/02/2023​
20​
2023​
2​
4066660​
Product 51
121001258​
20/02/2023​
60​
2023​
2​
3914280​
Product 51
121001258​
24/02/2023​
30​
2023​
2​
1957140​
Product 51
121001792​
04/01/2023​
131​
2023​
1​
8546178​
Product 51
121001792​
09/01/2023​
69​
2023​
1​
4501422​
Product 51
121001943​
04/01/2023​
-50​
2023​
1​
-3261900​
Product 51
121001943​
23/02/2023​
50​
2023​
2​
3261900​
Product 44
121002462​
14/01/2023​
10​
2023​
1​
1000000​
Product 44
121002462​
28/02/2023​
20​
2023​
2​
2000000​
Product 46
121002462​
14/01/2023​
10​
2023​
1​
2033330​
Product 46
121002462​
28/02/2023​
20​
2023​
2​
4066660​
Product 46
121002507​
14/01/2023​
20​
2023​
1​
4066660​
Product 51
121002507​
16/02/2023​
20​
2023​
2​
1304760​
Product 46
121002535​
07/02/2023​
4​
2023​
2​
813332​
Product 19
121002730​
13/02/2023​
10​
2023​
2​
985710​
Product 44
121002730​
10/01/2023​
20​
2023​
1​
2000000​
Product 44
121002730​
27/01/2023​
30​
2023​
1​
3000000​
Product 44
121002730​
13/02/2023​
30​
2023​
2​
3000000​
Product 44
121002730​
23/02/2023​
30​
2023​
2​
3000000​
Product 44
121002730​
28/02/2023​
20​
2023​
2​
2000000​
Product 46
121002730​
10/01/2023​
20​
2023​
1​
4066660​
Product 46
121002730​
27/01/2023​
30​
2023​
1​
6099990​
Product 46
121002730​
31/01/2023​
10​
2023​
1​
2033330​
Product 46
121002730​
31/01/2023​
10​
2023​
1​
2033330​
Product 46
121002730​
13/02/2023​
20​
2023​
2​
4066660​
Product 46
121002730​
23/02/2023​
20​
2023​
2​
4066660​
Product 46
121002730​
28/02/2023​
10​
2023​
2​
2033330​
Product 46
121002752​
14/01/2023​
10​
2023​
1​
2033330​
Product 44
121003038​
05/01/2023​
20​
2023​
1​
2000000​
Product 44
121003038​
21/02/2023​
20​
2023​
2​
2000000​
Product 44
121003038​
24/02/2023​
20​
2023​
2​
2000000​
Product 46
121003038​
05/01/2023​
25​
2023​
1​
5083325​
Product 46
121003038​
27/01/2023​
20​
2023​
1​
4066660​
Product 46
121003038​
28/01/2023​
10​
2023​
1​
2033330​
Product 46
121003038​
21/02/2023​
10​
2023​
2​
2033330​
Product 46
121003038​
24/02/2023​
20​
2023​
2​
4066660​
Product 44
121003326​
16/01/2023​
10​
2023​
1​
1000000​
Product 44
121003326​
27/01/2023​
30​
2023​
1​
3000000​
Product 51
121003349​
17/02/2023​
50​
2023​
2​
3261900​
Product 51
121003359​
16/01/2023​
41​
2023​
1​
2674758​
Product 44
121003423​
16/01/2023​
10​
2023​
1​
1000000​
Product 44
121003423​
27/01/2023​
20​
2023​
1​
2000000​
Product 46
121003423​
16/01/2023​
30​
2023​
1​
6099990​
Product 46
121003423​
27/01/2023​
30​
2023​
1​
6099990​
Product 44
121003739​
09/01/2023​
5​
2023​
1​
500000​
Product 44
121003739​
15/02/2023​
5​
2023​
2​
500000​
Product 46
121003739​
09/01/2023​
5​
2023​
1​
1016665​
 

Attachments

  • expect.JPG
    expect.JPG
    75.7 KB · Views: 10
Sort by date Sort by votes
Peter_SSs said:
So the count for customer code 121001943 should also be zero since that code has a negative total sales?

blader1989.xlsm
ABGHIJK
1Product NameCustomer CodeTotal SalesJan
2Product 4412100246210000001210024622
3Product 4412100273020000001210027301should be 1
4Product 44121002730-20000001210030381
5Product 4412100303820000001210010761
6Product 4612100107620333301210025071
7Product 4612100246220333301210027521
8Product 4612100250740666601210017921
9Product 4612100273040666601210019430??
10Product 461210027306099990
11Product 461210027302033330
12Product 461210027302033330
13Product 461210027522033330
14Product 511210017928546178
15Product 511210017924501422
16Product 51121001943-3261900
17
Sheet2
Cell Formulas
RangeFormula
I2:I9I2=UNIQUE(B2:B16)
J2:J9J2=BYROW(I2#,LAMBDA(r,IFNA(ROWS(UNIQUE(FILTER(A2:A16,(B2:B16=r)*(SUMIFS(G2:G16,A2:A16,A2:A16,B2:B16,r)>0),NA()))),0)))
Dynamic array formulas.
Click to expand...
You're right Peter, let me try your formula then I will come back to let you know the result.
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Thank you @dermie_72 and @Peter_SSs everything worked well now. I hope your solution can help all who seek the problem like me in future. If I have any question I will send the message to you.

Have a nice day!!! Cheers!
 
Upvote 0
Glad to see that your question has been resolved, but I would like to offer an alternative solution in case the PIVOTBY function is available.
Code: 
=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Book1.xlsx
ABCDEFGHIJKL
1Product NameCustomer CodeBilling DateQtyYearMonthTotal Sales
2Product 451210010762023-2-8520232864285 01 Jan02 Feb
3Product 461210010762023-1-41020231203333012100107612
4Product 461210010762023-2-1202023240666601210012581
5Product 511210012582023-2-20602023239142801210017921
6Product 511210012582023-2-243020232195714012100246222
7Product 511210017922023-1-413120231854617812100250711
8Product 511210017922023-1-9692023145014221210025351
9Product 511210019432023-1-4-5020231-326190012100273023
10Product 511210019432023-2-23502023232619001210027521
11Product 441210024622023-1-141020231100000012100303822
12Product 441210024622023-2-28202023220000001210033261
13Product 461210024622023-1-14102023120333301210033491
14Product 461210024622023-2-28202023240666601210033591
15Product 461210025072023-1-14202023140666601210034232
16Product 511210025072023-2-162020232130476012100373921
17Product 461210025352023-2-7420232813332
18Product 191210027302023-2-131020232985710
19Product 441210027302023-1-1020202312000000
20Product 441210027302023-1-2730202313000000
21Product 441210027302023-2-1330202323000000
22Product 441210027302023-2-2330202323000000
23Product 441210027302023-2-2820202322000000
24Product 461210027302023-1-1020202314066660
25Product 461210027302023-1-2730202316099990
26Product 461210027302023-1-3110202312033330
27Product 461210027302023-1-3110202312033330
28Product 461210027302023-2-1320202324066660
29Product 461210027302023-2-2320202324066660
30Product 461210027302023-2-2810202322033330
31Product 461210027522023-1-1410202312033330
32Product 441210030382023-1-520202312000000
33Product 441210030382023-2-2120202322000000
34Product 441210030382023-2-2420202322000000
35Product 461210030382023-1-525202315083325
36Product 461210030382023-1-2720202314066660
37Product 461210030382023-1-2810202312033330
38Product 461210030382023-2-2110202322033330
39Product 461210030382023-2-2420202324066660
40Product 441210033262023-1-1610202311000000
41Product 441210033262023-1-2730202313000000
42Product 511210033492023-2-1750202323261900
43Product 511210033592023-1-1641202312674758
44Product 441210034232023-1-1610202311000000
45Product 441210034232023-1-2720202312000000
46Product 461210034232023-1-1630202316099990
47Product 461210034232023-1-2730202316099990
48Product 441210037392023-1-9520231500000
49Product 441210037392023-2-15520232500000
50Product 461210037392023-1-9520231101666
51
52
Sheet1
Cell Formulas
RangeFormula
I2:K16I2=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Dynamic array formulas.
 
Upvote 1
shaowu459 said:
Glad to see that your question has been resolved, but I would like to offer an alternative solution in case the PIVOTBY function is available.
Code: 
=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Book1.xlsx
ABCDEFGHIJKL
1Product NameCustomer CodeBilling DateQtyYearMonthTotal Sales
2Product 451210010762023-2-8520232864285 01 Jan02 Feb
3Product 461210010762023-1-41020231203333012100107612
4Product 461210010762023-2-1202023240666601210012581
5Product 511210012582023-2-20602023239142801210017921
6Product 511210012582023-2-243020232195714012100246222
7Product 511210017922023-1-413120231854617812100250711
8Product 511210017922023-1-9692023145014221210025351
9Product 511210019432023-1-4-5020231-326190012100273023
10Product 511210019432023-2-23502023232619001210027521
11Product 441210024622023-1-141020231100000012100303822
12Product 441210024622023-2-28202023220000001210033261
13Product 461210024622023-1-14102023120333301210033491
14Product 461210024622023-2-28202023240666601210033591
15Product 461210025072023-1-14202023140666601210034232
16Product 511210025072023-2-162020232130476012100373921
17Product 461210025352023-2-7420232813332
18Product 191210027302023-2-131020232985710
19Product 441210027302023-1-1020202312000000
20Product 441210027302023-1-2730202313000000
21Product 441210027302023-2-1330202323000000
22Product 441210027302023-2-2330202323000000
23Product 441210027302023-2-2820202322000000
24Product 461210027302023-1-1020202314066660
25Product 461210027302023-1-2730202316099990
26Product 461210027302023-1-3110202312033330
27Product 461210027302023-1-3110202312033330
28Product 461210027302023-2-1320202324066660
29Product 461210027302023-2-2320202324066660
30Product 461210027302023-2-2810202322033330
31Product 461210027522023-1-1410202312033330
32Product 441210030382023-1-520202312000000
33Product 441210030382023-2-2120202322000000
34Product 441210030382023-2-2420202322000000
35Product 461210030382023-1-525202315083325
36Product 461210030382023-1-2720202314066660
37Product 461210030382023-1-2810202312033330
38Product 461210030382023-2-2110202322033330
39Product 461210030382023-2-2420202324066660
40Product 441210033262023-1-1610202311000000
41Product 441210033262023-1-2730202313000000
42Product 511210033492023-2-1750202323261900
43Product 511210033592023-1-1641202312674758
44Product 441210034232023-1-1610202311000000
45Product 441210034232023-1-2720202312000000
46Product 461210034232023-1-1630202316099990
47Product 461210034232023-1-2730202316099990
48Product 441210037392023-1-9520231500000
49Product 441210037392023-2-15520232500000
50Product 461210037392023-1-9520231101666
51
52
Sheet1
Cell Formulas
RangeFormula
I2:K16I2=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Dynamic array formulas.
Click to expand...
Hi @shaowu459 thank you for your solution but when I add your formula to my file, it shown NAME? so could you help me to correct the error?
Book1_SKU purchased segment.xlsx
ABCDEFGHI
1Product NameCustomer CodeBilling DateQtyYearMonthTotal Sales
2Product 4512100107644965520232864285#NAME?
3Product 461210010764493010202312033330
4Product 461210010764495820202324066660
5Product 511210012584497760202323914280
6Product 511210012584498130202321957140
7Product 5112100179244930131202318546178
8Product 511210017924493569202314501422
9Product 5112100194344930-5020231-3261900
10Product 511210019434498050202323261900
11Product 441210024624494010202311000000
12Product 441210024624498520202322000000
13Product 461210024624494010202312033330
14Product 461210024624498520202324066660
15Product 461210025074494020202314066660
16Product 511210025074497320202321304760
17Product 4612100253544964420232813332
18Product 19121002730449701020232985710
19Product 441210027304493620202312000000
20Product 441210027304495330202313000000
21Product 441210027304497030202323000000
22Product 441210027304498030202323000000
23Product 441210027304498520202322000000
24Product 461210027304493620202314066660
25Product 461210027304495330202316099990
26Product 461210027304495710202312033330
27Product 461210027304495710202312033330
28Product 461210027304497020202324066660
29Product 461210027304498020202324066660
30Product 461210027304498510202322033330
31Product 461210027524494010202312033330
32Product 441210030384493120202312000000
33Product 441210030384497820202322000000
34Product 441210030384498120202322000000
35Product 461210030384493125202315083325
36Product 461210030384495320202314066660
37Product 461210030384495410202312033330
38Product 461210030384497810202322033330
39Product 461210030384498120202324066660
40Product 441210033264494210202311000000
41Product 441210033264495330202313000000
42Product 511210033494497450202323261900
43Product 511210033594494241202312674758
44Product 441210034234494210202311000000
45Product 441210034234495320202312000000
46Product 461210034234494230202316099990
47Product 461210034234495330202316099990
48Product 4412100373944935520231500000
49Product 4412100373944972520232500000
50Product 4612100373944935520231101666
Sheet1
Cell Formulas
RangeFormula
I2I2=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
 
Upvote 0
blader1989 said:
it shown NAME? so could you help me to correct the error?
Click to expand...
It just means that you do not yet have the PIVOTBY functions. Notice that the suggestion was prefaced by ..
shaowu459 said:
in case the PIVOTBY function is available.
Click to expand...

So until you do get that function you would need to use the earlier solution.
 
Upvote 0
blader1989 said:
Hi @shaowu459 thank you for your solution but when I add your formula to my file, it shown NAME? so could you help me to correct the error?
Book1_SKU purchased segment.xlsx
ABCDEFGHI
1Product NameCustomer CodeBilling DateQtyYearMonthTotal Sales
2Product 4512100107644965520232864285#NAME?
3Product 461210010764493010202312033330
4Product 461210010764495820202324066660
5Product 511210012584497760202323914280
6Product 511210012584498130202321957140
7Product 5112100179244930131202318546178
8Product 511210017924493569202314501422
9Product 5112100194344930-5020231-3261900
10Product 511210019434498050202323261900
11Product 441210024624494010202311000000
12Product 441210024624498520202322000000
13Product 461210024624494010202312033330
14Product 461210024624498520202324066660
15Product 461210025074494020202314066660
16Product 511210025074497320202321304760
17Product 4612100253544964420232813332
18Product 19121002730449701020232985710
19Product 441210027304493620202312000000
20Product 441210027304495330202313000000
21Product 441210027304497030202323000000
22Product 441210027304498030202323000000
23Product 441210027304498520202322000000
24Product 461210027304493620202314066660
25Product 461210027304495330202316099990
26Product 461210027304495710202312033330
27Product 461210027304495710202312033330
28Product 461210027304497020202324066660
29Product 461210027304498020202324066660
30Product 461210027304498510202322033330
31Product 461210027524494010202312033330
32Product 441210030384493120202312000000
33Product 441210030384497820202322000000
34Product 441210030384498120202322000000
35Product 461210030384493125202315083325
36Product 461210030384495320202314066660
37Product 461210030384495410202312033330
38Product 461210030384497810202322033330
39Product 461210030384498120202324066660
40Product 441210033264494210202311000000
41Product 441210033264495330202313000000
42Product 511210033494497450202323261900
43Product 511210033594494241202312674758
44Product 441210034234494210202311000000
45Product 441210034234495320202312000000
46Product 461210034234494230202316099990
47Product 461210034234495330202316099990
48Product 4412100373944935520231500000
49Product 4412100373944972520232500000
50Product 4612100373944935520231101666
Sheet1
Cell Formulas
RangeFormula
I2I2=PIVOTBY(B2:B50,TEXT(C2:C50,"mm mmm"),A2:A50,LAMBDA(x,ROWS(UNIQUE(x))),,0,,0,,SUMIF(B:B,B2:B50,G:G)>0)
Click to expand...
For now, PIVOTBY function is only available for 365 insider program, beta channel. If you wish to try that solution, click File->Account->insider->beta channel, and finally updates. Then you can use PIVOTBY and GROUPBY functions.

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

Similar threads

K
  • Question Question
Populate Based on Actual and Budget
Replies
2
Views
77
kumara_faith
K
M
  • Question Question
Index Match function List based on date
Replies
7
Views
241
Fluff
Fluff
O
  • Solved
Macro to extract repeating invoice numbers and details
Replies
4
Views
222
Omar M
O
M
  • Question Question
Counting specific dates from multiple columns
Replies
2
Views
342
mattbird
M
M
  • Question Question
valid data input
Replies
5
Views
285
mwvirk
M

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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