SUMIFS Question

[kparadise]

Hello,

I have a table that contains 5 different groups of items. Each of those items have a price, on a separate reference table. I am trying to determine the total price of each group using a formula.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]ITEM 1
[/TD]
[TD]ITEM 2
[/TD]
[TD]ITEM 3
[/TD]
[TD]ITEM 4
[/TD]
[TD]TOTAL PRICE
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]apple
[/TD]
[TD]apple
[/TD]
[TD]orange
[/TD]
[TD]banana
[/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]orange
[/TD]
[TD]apple
[/TD]
[TD]orange
[/TD]
[TD]banana
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]orange
[/TD]
[TD]apple
[/TD]
[TD]orange
[/TD]
[TD]grape
[/TD]
[TD]17
[/TD]
[/TR]
</tbody>[/TABLE]

Reference table (this is on the same sheet. please follow the row and column headings)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Item
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]apple
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]orange
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD]banana
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD]grape
[/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to put a formula in E2, and drag it down.

 

[Gerald Higgins]

I can see how you calculate the prices for lines 3 and 4, but what about line 2, why is it 22 ?

Shouldn't it be 19 ?
Apple 6 + Apple 6 + Orange 5 + Banana 2 = 19 ?

 

[kparadise]

Ya...sorry I was just doing that really fast. Did not check it.

 

[Gerald Higgins]

OK, do you only ever have 4 items per line, or could there be dozens / hundreds / thousands of items per line ?

If only ever 4, a simple multiple lookup will work.

Something like this
=vlookup(A2,O9:P12,2,false)+vlookup(B2,O9:P12,2,false) . . .
which covers it for the first two values, I'm sure you can figure out how to extend it to four items.

 

[kparadise]

I will have 10 items per line...I was hoping there was a fast SUMIFS formula, rather than doing an index/match + index/match + index/match + etc.

 

[Gerald Higgins]

I can't imagine how SUMIFS would be applied here.
I can imagine POSSIBLY using COUNTIF, but I personally would still use VLOOKUP, or, as you say, Index / Match.

 

[Fluff]

How about

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ITEM 1	ITEM 2	ITEM 3	ITEM 4	TOTAL PRICE										Item	Price
2	apple	apple	orange	banana	19										apple	6
3	orange	apple	orange	banana	18										orange	5
4	orange	apple	orange	grape	17										banana	2
5															grape	1
Test

Cell Formulas
Range	Formula
E2	=SUMPRODUCT((A2:D2=$O$2:$O$5)*($P$2:$P$5))

 

[Gerald Higgins]

. . . I personally would still use VLOOKUP, or, as you say, Index / Match.

. . . or SUMPRODUCT, as Fluff said

 

[Finalfight40]

Something like this:

=SUM(IF(A2:D2=$O$9:$O$12, $P$9:$P$12, 0))

This is an array so entered with Ctrl + Shift + Enter.

 

[Eric W]

Another version:

E2: =SUMPRODUCT(SUMIFS($P$9:$P$12,$O$9:$O$12,A2:D2))