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.

	A	B	C	D	E
1	ITEM 1	ITEM 2	ITEM 3	ITEM 4	TOTAL PRICE
2	apple	apple	orange	banana	22
3	orange	apple	orange	banana	18
4	orange	apple	orange	grape	17

<tbody>
</tbody>

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

	O	P
8	Item	Price
9	apple	6
10	orange	5
11	banana	2
12	grape	1

<tbody>
</tbody>

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))