getting value from a different cell...?

[Samuel]

If anyone can help me with this, I would be greatful. I created a very
simplified version of my problem below, but an answer to this can
solve my real problem. I am at a loss on how to associate the expense
next to the name with the dollar value in another column, then
maintain a running sum for each individual.

I have an expense sheet that has expenses by name, (ie car) and
another that assigns a dollar value to that car.
Each name can have multiple expenses (ie below fred has car car misc
meal). Nbr 1 below.

Another section, Nbr 2 below, assigns an expense value to each item.

I need a formula that answers nbr 3 Cells B5 through B8. It says: this
person had these four items, it checks the value of the each, adds
them up and gives me a total expense....


1)
A B C D E
1 fred car car misc meal
2 mike meal car phone misc
3 jim meal phone car misc
4 bill phone misc meal meal

2)
A B
5 car 100
6 meal 50
7 phone 10
8 misc 25

3)
A B
9 fred 275<------- Need formula for this column
10 mike 185
11 jim 185
12 bill 135

Any help greatfully appreciated!

 

[Dave Peterson]

I would do this.

I'd find x contiguous columns (H:xxx???) and put this in row one of those
columns:
Car meal Phone Misc xxx yyyy zzzz

Then in each row of real data (starts with row 2):

In H2:
=countif($b2:$e2,h$1)*vlookup(h$1,sheet2!$a:$b,2,false)

The $ signs are important. When the formula is dragged across and down the
range, the addresses will change nicely.

it counts the number of Cars in B:E and multiplies that by the value in the
table in sheet2.

Then I'd use another column that just summed those "detail" amounts.

=sub(h2:xxxx2)

Maybe even put that sum column to the left of those detail amounts.

====
I like keeping the details (even if I hide those columns for reporting). But
then I can do nice things--data|Filter|autofilter or data|subtotals or
data|pivottable to get summaries for each category.

 

[Guest]

This works but may not be suitable for a more complex version of your
requirement:


H1:I4 contains your table (section Nbr 2)

A10:A13 contains section Nbr 3

Put in B10 and copy down

=SUMPRODUCT(($A$1:$A$4=$A10)*(($B$1:$E$4=$H$1)*$I$1+($B$1:$E$4=$H$2)*$I$2+($B$1:$E$4=$H$3)*$I$3+($B$1:$E$4=$H$4)*$I$4))

I am sure there is a more "sophisticated" way to do this.

 

[Samuel]

This works but may not be suitable for a more complex version of your
requirement:

H1:I4 contains your table (section Nbr 2)

A10:A13 contains section Nbr 3

Put in B10 and copy down

=SUMPRODUCT(($A$1:$A$4=$A10)*(($B$1:$E$4=$H$1)*$I$1+($B$1:$E$4=$H$2)*$I$2+(­$B$1:$E$4=$H$3)*$I$3+($B$1:$E$4=$H$4)*$I$4))

I am sure there is a more "sophisticated" way to do this.












- Show quoted text -

Thank you both.
I understand what you are each saying and I think I can work with it.
Thanks