VLOOKUP and SUMIFS

[bmpreston]

I’m trying to add values in a column on a different table within the same workbook, if multiple criteria are met (that’s the sumifs part).

But I need to use a VLOOKUP to find those values to be added from one sheet to another.

 

[Special-K99]

More info needed supply some spreadsheet data.

 

[bmpreston]

I have two worksheets, one is 10 x infinite, here is our example to work with

	A	B	C	D	E	F	G	H	I	J
1	Name	Dollar Amount	Description	Part Number	Address	States	Country	(Irrelevant Information)	(Irrelevant information)	Quantity
2	Bob	$1000.00	Apples	10002000	Route 57	New York	United States	Red	Lincoln	1
3	Fred	$3000.00	Pears	10002100	Route 66	California	Mexico	Blue	Ford	3
4	George	$2500.00	Grapes	10401010	Route 166	Arizona	Australia	Gray	Toyota	5
5	Gerard	$4500.00	Apricots	10398392	Route 87	Washington	England	Black	Hyundai	2
6	Brandi	$6500.00	Strawberries	18393029	Route 328	North Carolina	China	Brown	Chevy	6
7	Bob	$5000.00	Pears	10002100	Route 166	Arizona	Australia	Gray	Toyota	5
	CONTINUES for several rows down-->>>VVV

<tbody>
</tbody>

Note that some information such as price is a multiplier of the quantity, but the location information may change. Also note that the part number determines the description (as any barcode would)

I need to use a VLOOKUP that has a complete list of all part numbers in sequential order (3000+ numbers) with their corresponding descriptions and base price.

The SUMIFS and VLOOKUP needs to check how many of each Bob used, and sum each up.

It would look like this if running a Bob report:

Bob Report	A	B	C	D
1	5000.00	Pears	10002100	5
2	1000.00	Apples	10002000	1

<tbody>
</tbody>

And I would run a separate report for each person, Bob, Brandi, Gerard, etc

Most is easy, this big formula is difficult.

Everything about this is easy regarding total pricing (just a simple multiplication, the description is an easy VLOOKUP from the table, but the sumif-vlookup from the other table, with multiple criteria is hard. Criteria if name matches, and part number matches, to then add up cell values.

Thanks again.

 

[Special-K99]

It looks like you're saying you have a list of parts bought by lots of different customers and want to produce a smaller list for each customer just for the parts they bought.

First problem is the order of this output will be the order on the original spreadsheet, not the part number order.

Assuming data is in Sheet1

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$2:$J$1000,SMALL(IF((Sheet1!$A$2:$A$1000="Bob"),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),2),"")

in Sheet2!B1
=IFERROR(INDEX(Sheet1!$A$2:$J$1000,SMALL(IF((Sheet1!$A$2:$A$1000="Bob"),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),3),"")

in Sheet2!C1
=IFERROR(INDEX(Sheet1!$A$2:$J$1000,SMALL(IF((Sheet1!$A$2:$A$1000="Bob"),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),4),"")

in Sheet2!D1
=IFERROR(INDEX(Sheet1!$A$2:$J$1000,SMALL(IF((Sheet1!$A$2:$A$1000="Bob"),ROW($A$2:$A$1000)),ROW(A1))-(ROW(A$2)-1),10),"")

NOTE: These are all array formulas
Array formula, use Ctrl-Shift-Enter

Enter the four formulas in a row one at a time.
Then copy them downwards
You should copy them for as many rows as you have on the original sheet.

 

[bmpreston]

Ok. This is working quite well. I moved a few things around as the column number was off (not 2, 3, 4, and 10 respectively, which isn't that big of a deal, but its not accumulating the results.

It's simply pulling out where my name is and bringing those values over, even if the part number is duplicated.

I'm looking to add up the total parts used (part numbers and descriptions) which may have been used across different locations, so therefore its an accumulation.

Thanks