SUMIF meets criteria in another column and divide by particular amounts

[adidasfreek]

Hello all, I posted this elsewhere but wanted to try again to see if I got any other responses. I want to work out the cost per person.....here's some sample data so you can see what I mean.

Cost Split
10 ALL
20 ALL
30 AA
40 AB
10 ALL
20 AB
30 EACH
40 ALL
10 AA
20 EACH

Key
ALL=split between everyone
AA=split between Amy and Brian
AB= split between Carla and David
EACH = add amount to each person

Total amount owed each Name: Amy, Brian, Carla, David

Sorry couldn't format that terribly well. Would welcome your input.

 

[dermie_72]

Your table looks like this

	A	B
1	10	ALL
2	20	ALL
3	30	AA
4	40	AB
5	10	ALL
6	20	AB
7	30	EACH
8	40	ALL
9	10	AA
10	20	EACH

<TBODY>
</TBODY>

You could use this formula:

	A	B	C
21	Amy	=(SUMIF(B1:B10,"ALL",A1:A10)/4)+(SUMIF(B1:B10,"AA",A1:A10)/2)+(SUMIF(B1:B10,"EACH",A1:A10))	90
22	Brian	=(SUMIF(B1:B10,"ALL",A1:A10)/4)+(SUMIF(B1:B10,"AA",A1:A10)/2)+(SUMIF(B1:B10,"EACH",A1:A10))	90
23	Carla	(SUMIF(B1:B10,"ALL",A1:A10)/4)+(SUMIF(B3:B12,"AB",A3:A12)/2)+SUMIF(B4:B13,"EACH",A4:A13)	100
24	David	(SUMIF(B1:B10,"ALL",A1:A10)/4)+(SUMIF(B3:B12,"AB",A3:A12)/2)+SUMIF(B4:B13,"EACH",A4:A13)	100

<TBODY>
</TBODY>

 

[adidasfreek]

Thanks so much, this is exactly what I wanted!