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.
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.
When I step through the code with the evaluate ffunction I see a #name? error but it doesm't make sense to me.
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.
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
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 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | PN | TSV | PN | TSV | PN | TSV | PN | TSV | 19 | ||||
2 | 23164399 | € 3.965,00 | 10304539 | € 1.074,00 | 25005452 | € 924,00 | 23649195 | € 514,00 | TSV Total | ||||
3 | 23164399 | € 28.900,00 | 23164399 | € 1.315,00 | 23164399 | € 985,00 | 23164399 | € 180,00 | 0 | € - | |||
4 | 20942273 | € 17.896,00 | 27860306 | € 1.089,00 | 27860306 | € 416,00 | 27860306 | € 62,00 | € - | ||||
5 | 26853444 | € 25.160,00 | 20942273 | € 1.655,00 | 22595251 | € 660,00 | 22595251 | € 646,00 | € - | ||||
6 | 20487002 | € 920,00 | 26853444 | € 164,00 | 26853444 | € 191,00 | 26853444 | € 1.581,00 | € - | ||||
7 | 25246593 | € 1.508,00 | 20487002 | € 384,00 | 20487002 | € 1.180,00 | 20487002 | € 595,00 | € - | ||||
8 | 25215555 | € 1.193,00 | 25246593 | € 1.358,00 | 25246593 | € 340,00 | 25246593 | € 471,00 | € - | ||||
9 | 26672396 | € 1.666,00 | 28458859 | € 715,00 | 28458859 | € 1.856,00 | 28458859 | € 961,00 | € - | ||||
10 | 21312158 | € 38,00 | 25215555 | € 300,00 | 10518203 | € 196,00 | 10518203 | € 510,00 | € - | ||||
11 | 10545526 | € 421,00 | 10518203 | € 1.409,00 | 21312158 | € 888,00 | 21312158 | € 682,00 | € - | ||||
12 | 25136767 | € 148,00 | 21312158 | € 1.858,00 | 10545526 | € 546,00 | 10545526 | € 90,00 | € - | ||||
13 | 28363813 | € 211,00 | 10545526 | € 1.061,00 | 25136767 | € 1.746,00 | 25136767 | € 965,00 | € - | ||||
14 | 25005452 | € 664,00 | 25136767 | € 180,00 | 28363813 | € 1.502,00 | 28363813 | € 413,00 | € - | ||||
15 | 24996129 | € 257,00 | 28363813 | € 1.516,00 | 25005452 | € 546,00 | 25005452 | € 1.730,00 | € - | ||||
16 | 10304539 | € 1,00 | 25005452 | € 804,00 | 23649195 | € 1.348,00 | 23649195 | € 1.178,00 | € - | ||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J1 | J1 | =SUM(IF(FREQUENCY(IF(PNdata<>"",MATCH("~"&PNdata,PNdata&"",0)),Ivec),1)) |
J3 | J3 | =IF(ROWS($J$3:J3)<=$J$1,MIN(IF(ISNUMBER(MATCH(PNdata,$J$2:J2,0)),””,PNdata)),””) |
K3:K16 | K3 | =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.
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.