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:

UPC #	Actual Units Sold
786936856934 <tbody> </tbody>
786936858136 <tbody> </tbody>
786936859119 <tbody> </tbody>
786936856972 <tbody> </tbody>
786936858990 <tbody> </tbody>
786936859300 <tbody> </tbody>

<tbody>
</tbody>

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

UPC #	Actual Units Sold
786936856934	506
786936856934	37
786936856934	189
786936858136	22
786936858136	806
786936859119	224

<tbody>
</tbody>

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
1	UPC #	Actual Units Sold		UPC #	Actual Units Sold
2	786936856934	732		786936856934	506
3	786936858136	828		786936856934	37
4	786936859119	224		786936856934	189
5	786936856972	0		786936858136	22
6	786936858990	0		786936858136	806
7	786936859300	0		786936859119	224

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula B2 =SUMIF(D:D,A2,E:E) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!