constructing a sumif statement based on dropdown lists

[Rameses]

Hi all

I wish to create a sum formula based on if statements and using dropdown boxes

my worksheet has 2 headings
A1 Staff
B1 Time
C1 Cost

Row 2 onwards for Column A and Column B are based on dropdown lists
A2 Dropdown list is CT, TA, AEN
B2 Dropdown list is 1, 0.75, 0.5, 0.25
In C2 I want to work out the total cost

In E2:E4 is a list of the Staff
E2 CT
E3 TA
E4 AEN

In F2:F4 is a list of the Costs per Hour
F2 30
F3 14
F4 11

I want to put in a formula into column C that will say for example
if A2 =CT, and B2 =0.75 then Cost =F2*B2 Therefore C1 should =22.5

I hope this makes sense

Thanks for any help

Rameses

Worked example below
[TABLE="width: 300"]
<TBODY>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]STAFF[/TD]
[TD]TIME[/TD]
[TD]COST[/TD]
[TD][/TD]
[TD]Staff[/TD]
[TD]Hourly Cost[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]CT[/TD]
[TD]0.75[/TD]
[TD]22.5[/TD]
[TD][/TD]
[TD]CT[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]AEN[/TD]
[TD]0.5[/TD]
[TD]5.50[/TD]
[TD][/TD]
[TD]TA[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]TA[/TD]
[TD]1[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]AEN[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Mindpsyche]

Would that not be an If function:

Try this formula in the cell which needs the desired output.

=IF(AND(A2="CT",B2="0.75"),F2*B2,"")

 

[Rameses]

Thanks for getting back to me

I have reworked your example to be =IF(AND(A2,B2),F2*B2,"")

Whilst that works for the first example it does not work for the rest as it does not
recognise the change in cost for the other types of staff l. I know this is something to do with the use of dropdowns and linking the staff member to the cost before working out C.

any ideas?

Would that not be an If function:

Try this formula in the cell which needs the desired output.

=IF(AND(A2="CT",B2="0.75"),F2*B2,"")

 

[Rameses]

bump

 

[Rameses]

I have broken up and examined suggested forumla again
the issues it brings are that it fails to recognise
a) change in drop down for staff
b) difference in cost for staff if staff is changed ie, CT to AEN
c) change in drop down for time

any ideas anyone?

 

[Mindpsyche]

This formula:

=IF(AND(A2,B2),F2*B2,"")

What is the purpose of AND function? You have assigned 2 cells A2 and B2 but you have not given any conditions for them so even if you change the drop downs you will always get F2*B2.

Look at this sample below and see if this is what you need, formulas are at the bottom which need to be entered in Cell C2 and dragged down.

Excel 2003
	A	B	C	D	E
1	STAFF	TIME	COST	Staff	Hourly Cost
2	CT	0.75	22.5	CT	30
3	TA	0.5	7.0	TA	14
4	AEN	1	11.0	AEN	11
Sheet1

Cell Formulas
Range	Formula
C2	=INDEX($E$2:$E$4,MATCH(A2,$D$2:$D$4,0))*B2
C3	=INDEX($E$2:$E$4,MATCH(A3,$D$2:$D$4,0))*B3
C4	=INDEX($E$2:$E$4,MATCH(A4,$D$2:$D$4,0))*B4

 

[Rameses]

Hi Mindpsyche

Brilliant that works

Thankyou for taking the time to help

rameses

 

[Mindpsyche]

Your welcome. Thanks for the feedback.