Sum sales figures of each individual PN where PNs and sales figures are displayed in random cells

S

strooman

Active Member
Joined
Oct 29, 2013
Messages
339
Office Version
  1. 2016
Platform
  1. Windows
I want to add up the sales figures of each PN (Personal Number). Of course there is a problem, the PNs and sales figures are displayed in random cells and can change all the time. I stumbled on a identical post that can solve my problem:
Credits go to:
Code: Juan Pablo González
Spec: Aladin Akyurek
Source: Counting Values Associated with Changing Identifier Numbers
May 4, 2003

Unfortunately, I can't get it to work.
First I copied the necessary vba code.

VBA Code: 
Function ArrayUnion(ParamArray Arg() As Variant) As Variant
' Code: Juan Pablo González
' Spec: Aladin Akyurek
' May 4, 2003
' Ref: TinyURL.com - shorten that long URL into a tiny URL
Dim TempUnion() As Variant
Dim i As Long, Itm As Variant, Ctr As Long
    For i = LBound(Arg) To UBound(Arg)
        Arg(i) = Arg(i)
        If IsArray(Arg(i)) Then
            For Each Itm In Arg(i)
                Ctr = Ctr + 1
                ReDim Preserve TempUnion(1 To Ctr) As Variant
                TempUnion(Ctr) = Itm
            Next Itm
        Else
            Ctr = Ctr + 1
            ReDim Preserve TempUnion(1 To Ctr) As Variant
            TempUnion(Ctr) = Arg(i)
        End If
    Next i
ArrayUnion = TempUnion
End Function
Then I put the formulas to work
Define PNdata by means of Formulas | Name Manager as referring to:
=arrayunion(Sheet1!$A$2:$A$12;Sheet1!$C$2:$C$12;Sheet1!$E$2:$E$12;Sheet1!$G$2:$G$12)

Define Ivec as referring to:
=ROW(INDIRECT(“1:”&COLUMNS(PNdata)))

J1, control+shift+enter, not just enter:
=SUM(IF(FREQUENCY(IF(PNdata<>””;MATCH(“~”&PNdata;PNdata&””;0));Ivec);1))

J3, control+shift+enter, not just enter, and copy down:
=IF(ROWS($J$3:J3)<=$J$1;MIN(IF(ISNUMBER(MATCH(PNdata;$J$2:J2;0));””;PNdata));””)

K3, just enter and copy down:
=SUMIFS(B:H;A:G;J3)

unfortunately I can't get it to work and I get an error in cell J3 and when I copy down it all shows zero.

Waarden optellen terwijl Id telkens verandert.xlsm
ABCDEFGHIJK
1PNTSVPNTSVPNTSVPNTSV19
223164399€ 3.965,0010304539€ 1.074,0025005452€ 924,0023649195€ 514,00TSV Total
323164399€ 28.900,0023164399€ 1.315,0023164399€ 985,0023164399€ 180,000€ -
420942273€ 17.896,0027860306€ 1.089,0027860306€ 416,0027860306€ 62,00€ -
526853444€ 25.160,0020942273€ 1.655,0022595251€ 660,0022595251€ 646,00€ -
620487002€ 920,0026853444€ 164,0026853444€ 191,0026853444€ 1.581,00€ -
725246593€ 1.508,0020487002€ 384,0020487002€ 1.180,0020487002€ 595,00€ -
825215555€ 1.193,0025246593€ 1.358,0025246593€ 340,0025246593€ 471,00€ -
926672396€ 1.666,0028458859€ 715,0028458859€ 1.856,0028458859€ 961,00€ -
1021312158€ 38,0025215555€ 300,0010518203€ 196,0010518203€ 510,00€ -
1110545526€ 421,0010518203€ 1.409,0021312158€ 888,0021312158€ 682,00€ -
1225136767€ 148,0021312158€ 1.858,0010545526€ 546,0010545526€ 90,00€ -
1328363813€ 211,0010545526€ 1.061,0025136767€ 1.746,0025136767€ 965,00€ -
1425005452€ 664,0025136767€ 180,0028363813€ 1.502,0028363813€ 413,00€ -
1524996129€ 257,0028363813€ 1.516,0025005452€ 546,0025005452€ 1.730,00€ -
1610304539€ 1,0025005452€ 804,0023649195€ 1.348,0023649195€ 1.178,00€ -
Sheet1
Cell Formulas
RangeFormula
J1J1=SUM(IF(FREQUENCY(IF(PNdata<>"",MATCH("~"&PNdata,PNdata&"",0)),Ivec),1))
J3J3=IF(ROWS($J$3:J3)<=$J$1,MIN(IF(ISNUMBER(MATCH(PNdata,$J$2:J2,0)),””,PNdata)),””)
K3:K16K3=SUMIFS(B:H,A:G,J3)
Press CTRL+SHIFT+ENTER to enter array formulas.


When I step through the code with the evaluate ffunction I see a #name? error but it doesm't make sense to me.
Naamloos.png

What am I doing wrong? How can I adjust this setup to make it work so that the procedure add up the sales figures of each PN (Personal Number) and display the result in column K.
 
Sort by date Sort by votes
Your code and formulae work for me (column M just shows the easy way to do this in Excel 365, which unfortunately it looks you don't have).

Note that my formula separator is ","rather than your ";".

All I had to do to get it working was to change the two empty string values in J3 (which were causing the #NAME? error) from:

1738303157150.png


to:

=IF(ROWS($J$3:J3)<=$J$1,MIN(IF(ISNUMBER(MATCH(PNdata,$J$2:J2,0)),””,PNdata)),””)

(I also extended PNdata from row 12 to row 16, so that there are 19 unique PNs, as you have shown in J1).

ABCDEFGHIJKLM
1PNTSVPNTSVPNTSVPNTSV19
22316439939651030453910742500545292423649195514TSV TotalExcel365
32316439928900231643991315231643999852316439918010304539107510304539
4209422731789627860306108927860306416278603066210518203211510518203
52685344425160209422731655225952516602259525164610545526211810545526
620487002920268534441642685344419126853444158120487002307920487002
72524659315082048700238420487002118020487002595209422731955120942273
8252155551193252465931358252465933402524659347121312158346621312158
9266723961666284588597152845885918562845885996122595251130622595251
102131215838252155553001051820319610518203510231643993534523164399
1110545526421105182031409213121588882131215868223649195304023649195
12251367671482131215818581054552654610545526902499612925724996129
13283638132111054552610612513676717462513676796525005452466825005452
1425005452664251367671802836381315022836381341325136767303925136767
15249961292572836381315162500545254625005452173025215555149325215555
161030453912500545280423649195134823649195117825246593367725246593
1726672396166626672396
18268534442709626853444
1927860306156727860306
2028363813364228363813
2128458859353228458859
22 121732
23CHECK121732
24
Sheet1
Cell Formulas
RangeFormula
J1J1=SUM(IF(FREQUENCY(IF(PNdata<>"",MATCH("~"&PNdata,PNdata&"",0)),IVEC),1))
M3:M21M3=SORT(UNIQUE(VSTACK(A2:A16,C2:C16,E2:E16,G2:G16)))
J3:J22J3=IF(ROWS($J$3:J3)<=$J$1,MIN(IF(ISNUMBER(MATCH(PNdata,$J$2:J2,0)),"",PNdata)),"")
K3:K21K3=SUMIFS(B:H,A:G,J3)
K22K22=SUM(K3:K21)
K23K23=SUM((A2:H16)*(A1:H1="TSV"))
Dynamic array formulas.
 
Upvote 0
With Power Query

Book2
JK
1PNTotal Sales
22316439935345
3103045391075
4250054524668
5236491953040
62094227319551
7278603061567
82685344427096
9225952511306
10204870023079
11252465933677
12252155551493
13266723961666
14284588593532
15213121583466
16105182032115
17105455262118
18251367673039
19283638133642
2024996129257
Sheet1


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", Int64.Type}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Attribute.1]= "TSV" then [Value] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Attribute.1] = "PN")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Value", "PN"}, {"Custom", "TSV"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"PN"}, {{"Total Sales", each List.Sum([TSV]), type number}})
in
    #"Grouped Rows"
 
Upvote 0
Thanks for the corrections and advice, I got it working.
Power Query is not my field of expertise so I will have to look into that first to fully understand it.
 
Upvote 0
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
www.excelcampus.com

The Complete Guide to Installing Power Query - Excel Campus

Learn what version you need to get Power Query and instructions on how to install this free add-in from Microsoft.
www.excelcampus.com www.excelcampus.com

- Follow this link for an introduction to Power Query functionality.
www.excelcampus.com

Power Query Overview: An Introduction to Excel's Most Powerful Data Tool - Excel Campus

Learn how this FREE Excel add-in from Microsoft called Power Query will help transform your data into a format that is ready for pivot tables, reports and more.
www.excelcampus.com www.excelcampus.com

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query - How To Paste Code (video) - Excel Solutions

Power Query is great at solving data transformation and consolidation issues. But there are still loads of Excel users who have never used Power Query yet – and sometimes when they’re given some Power Query code to answer their question, they don’t know what to do with it. Here’s a short video...
excel.solutions
 
Upvote 0
We also have a forum where you can ask Power Query questions here: Power Tools
 
Upvote 0
@alansidman and Joe4, thanks for your information, I appreciate that and will definitely look into it. Never used it. Most of the time I stick to the well-known routines while sometimes it is better to broaden my knowledge

​

 
Upvote 0
Firstly, I have not checked all the code, names, formulas etc from post 1 but it may be that the only problem with that solution is that your J3 formula has some slanted quotes ”” where they should be straight quotes "".

Also, your signature does suggest that you also have Microsoft 365,
1738365580270.png

.. in which case you might be able to use this with that version?

strooman.xlsm
ABCDEFGHIJK
1PNTSVPNTSVPNTSVPNTSVPNTSV Total
22316439939651030453910742500545292423649195514103045391075
323164399289002316439913152316439998523164399180105182032115
42094227317896278603061089278603064162786030662105455262118
526853444251602094227316552259525166022595251646204870023079
62048700292026853444164268534441912685344415812094227319551
72524659315082048700238420487002118020487002595213121583466
82521555511932524659313582524659334025246593471225952511306
926672396166628458859715284588591856284588599612316439935345
102131215838252155553001051820319610518203510236491953040
1110545526421105182031409213121588882131215868224996129257
1225136767148213121581858105455265461054552690250054524668
132836381321110545526106125136767174625136767965251367673039
14250054526642513676718028363813150228363813413252155551493
152499612925728363813151625005452546250054521730252465933677
1610304539125005452804236491951348236491951178266723961666
172685344427096
18278603061567
19283638133642
20284588593532
21Total121732
22
Sheet1
Cell Formulas
RangeFormula
J1:K21J1=LET(d,WRAPROWS(TOCOL(A2:H16),2),VSTACK({"PN","TSV Total"},GROUPBY(TAKE(d,,1),TAKE(d,,-1),SUM)))
Dynamic array formulas.
 
Last edited:
Upvote 0
Peter_SSs said:
Firstly, I have not checked all the code, names, formulas etc from post 1 but it may be that the only problem with that solution is that your J3 formula has some slanted quotes ”” where they should be straight quotes "".

Also, your signature does suggest that you also have Microsoft 365, View attachment 121882
.. in which case you might be able to use this with that version?
Click to expand...
The slanted quotes where already solved by StephenCrump.

I have Microsoft 365 Basic but I run into to much problems to implement your suggestion. I got #name? errors and it doesn't have VBA. Some problems have to do with language settings but I'm unable to change that setting in my 365 Basic version. It stays on Dutch. Microsoft 365 give me to much hassle and I hardly use it. So I stick with office on my laptop. Windows 11 and office where preinstalled when I bought the laptop. Excuses for elaborating because this is beyond the scope of this topic.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Adding CountIf to Array Formula
Replies
1
Views
331
MattH1
M

Forum statistics

Threads
1,226,882
Messages
6,193,481
Members
453,803
Latest member
hbvba

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