vlookup alternative for array of data?

[haleysmithc]

I am trying to get a total of units sold for a particular UPC. The units are not summed up before they reach me, so vlookup does not work here--first I need to get the total of the units, and then I need to input that total. I usually use a pivot table for this, but recently I've run into issues and started to wonder if it was possible to do this with a formula.

This is where I am trying to total and gather the info:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]UPC #[/TD]
[TD="align: center"]Actual Units Sold[/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936856934[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936858136[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936859119[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936856972[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936858990[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<tbody>[TR]
[TD="class: xl4200, width: 92, align: right"]786936859300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


But the info from the table where I am pulling from looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]UPC #[/TD]
[TD="align: center"]Actual Units Sold[/TD]
[/TR]
[TR]
[TD]786936856934
[/TD]
[TD]506[/TD]
[/TR]
[TR]
[TD]786936856934
[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]786936856934
[/TD]
[TD]189[/TD]
[/TR]
[TR]
[TD]786936858136
[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]786936858136
[/TD]
[TD]806[/TD]
[/TR]
[TR]
[TD]786936859119[/TD]
[TD]224[/TD]
[/TR]
</tbody>[/TABLE]
Etc, etc... (the current table I'm working with has about ~4500 rows)

I assume some form of array would accomplish this, but after researching it I am not sure I am able to find the one I need. Any help is appreciated!

 

[Eric W]

Welcome to the Board!

There is a built-in function to do just what you want:

	A	B	C	D	E	F
UPC #	Actual Units Sold
786936856934
786936856934
786936856934
786936858136
786936858136
786936859119

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]UPC #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: center"]Actual Units Sold[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936856934[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]732[/TD]
[TD="align: right"][/TD]

[TD="align: right"]506[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936858136[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]828[/TD]
[TD="align: right"][/TD]

[TD="align: right"]37[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936859119[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]224[/TD]
[TD="align: right"][/TD]

[TD="align: right"]189[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936856972[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936858990[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]806[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]786936859300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"]224[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=SUMIF(D:D,A2,E:E)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I assume your numbers are stored as text, otherwise Excel will convert them to scientific notation, and you'll lose the significant digits.

Hope this helps!

 

[haleysmithc]

Wow, I didn't even know about that! I had no idea the solution would be so simple. Thank you

 

[Eric W]

You're welcome!