Excel help with SUMIF (I think)

[crackers85]

Hi Everyone,



I have fairly basic excel knowledge and was looking for some help regarding new starters that would affect budgets. What i want to do is be able to select from a drop down a a job title and a month start. This would then generate a cost for the rest of the year based on the month start plus some additional costs. On one tab I have the drop downs for Job Role and Month created and then in another tab i have all the costs listed, looking like this:

Employee Type	Salary + NI + Pension	Month Start	Additional Items Fixed	Cost	Oter Items Monthly	Annual Cost	Software Suscriptions	Cost
Head of Business	£ 70,680.00	January	Laptop	£ 850.00	Phone	£ 600.00	WebHR	£ 42.00
Implementation Manager	£ 38,285.00	February	Tablet	£ 200.00			Teamwork	£ 144.00
Senior Developer	£ 47,120.00	March	Bag	£ 30.00			Lucid Chart	£ 90.00
Junior Developer	£ 29,450.00	April	Mouse	£ 15.00			SmartSheets	£ 240.00
Business Graduate	£ 22,383.00	May					Microsoft	£ 192.00
Customer Support	£ 24,738.00	June					Freshdesk / Freshchat	£ 612.00
Australian Implementation Manager	£ 62,963.00	July
		August
		September
		October
		November
		December

If i have a cost column in the first tab beside job role and month start, what formula would i need to work out the annual cost, depending on start date. Everyone gets a laptop, however only some roles get phones.



Any help greatly appreciated.

 

[GraH]

Hi *******s85,
Would this alternative do it for you

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Employee Type	Salary + NI + Pension	SW Year Tot	Items Fixed Yearly Tot	Monthly		Additional Items Fixed	Cost	Oter Items Monthly	Annual Cost	Software Suscriptions	Cost		Month Start
2	Head of Business	£ 70.680,00	£ 1.320,00	£ 1.095,00	£ 6.091,25		Laptop	£ 850,00	Phone	£ 600,00	WebHR	£ 42,00		January
3	Implementation Manager	£ 38.285,00	£ 1.320,00	£ 1.095,00	£ 3.391,67		Tablet	£ 200,00	Phone Monthly	£ 50,00	Teamwork	£ 144,00		February
4	Senior Developer	£ 47.120,00	£ 1.320,00	£ 1.095,00	£ 4.127,92		Bag	£ 30,00			Lucid Chart	£ 90,00		March
5	Junior Developer	£ 29.450,00	£ 1.320,00	£ 1.095,00	£ 2.655,42		Mouse	£ 15,00			SmartSheets	£ 240,00		April
6	Business Graduate	£ 22.383,00	£ 1.320,00	£ 1.095,00	£ 2.066,50						Microsoft	£ 192,00		May
7	Customer Support	£ 24.738,00	£ 1.320,00	£ 1.095,00	£ 2.262,75						Freshdesk / Freshchat	£ 612,00		June
8	Australian Implementation Manager	£ 62.963,00	£ 1.320,00	£ 1.095,00	£ 5.448,17									July
9														August
10														September
11														October
12														November
13														December
14	Job Role	Month	Incl Phone	Excl Phone
15	Senior Developer	June	£ 29.245,42	£ 28.895,42
16
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=SUM($L$2:$L$7)
D2:D8	D2	=SUM($H$2:$H$5)
E2:E8	E2	=SUM(B2:D2)/12
J3	J3	=J2/12
C15	C15	=(INDEX($E$2:$E$8,MATCH(A15,$A$2:$A$8,0))+vPhoneCost)*(13-MATCH(B15,$N$2:$N$13,0))
D15	D15	=INDEX($E$2:$E$8,MATCH(A15,$A$2:$A$8,0))*(13-MATCH(B15,$N$2:$N$13,0))

Named Ranges
Name	Refers To	Cells
vPhoneCost	=Sheet1!$J$3	C15

Cells with Data Validation
Cell	Allow	Criteria
A15	List	=$A$2:$A$8
B15	List	=$N$2:$N$13

 

[crackers85]

Not exactly what i was looking for but this has been a massive help as i have managed to adjust to suit my needs.

Thanks for your help!

 

[GraH]

I have managed to adjust to suit my needs.
Thanks for your help!

That's great!
My pleasure.

 

[KRice]

Here's another idea just in case you're still considering some options. This changes the layout of your tables so that you can assign (with a "1") the software and equipment for each position. Then monthly and fixed costs are computed in columns N and O. The selection of employee type, start month, and ROY (remainder of year) cost is shown in R2:R4.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1																				Month Start
2	Expense type	Salary & Benefits	Office Equipment					Software Suscriptions						Costs	Costs		Employee Type -->	Business Graduate		January
3	Cost		850	200	30	15	50	42	144	90	240	192	612				Month Start -->	July		February
4	Cost frequency	annual	fixed	fixed	fixed	fixed	month	annual	annual	annual	annual	annual	annual	Monthly	Fixed		ROY Cost -->	11191.50		March
5	Employee Type	Salary + NI + Pension	Laptop	Tablet	Bag	Mouse	Phone	WebHR	Teamwork	Lucid Chart	SmartSheets	Microsoft	Freshdesk / Freshchat							April
6	Head of Business	70,680.00	1	1	1	1	1	1	1	1	1	1	1	6050.00	1095.00					May
7	Implementation Manager	38,285.00	1	1	1	1	1							3240.42	1095.00					June
8	Senior Developer	47,120.00							1	1			1	3997.17	0.00					July
9	Junior Developer	29,450.00	1			1	1		1			1		2532.17	865.00					August
10	Business Graduate	22,383.00												1865.25	0.00					September
11	Customer Support	24,738.00	1				1				1	1	1	2198.50	850.00					October
12	Australian Implementation Manager	62,963.00												5246.92	0.00					November
13																				December
14
crackers85

Cell Formulas
Range	Formula
G3	G3	=600/12
R4	R4	=SUMPRODUCT((N6:N12)*(DATEDIF(LEFT(R3,3)&"-"&1,DATE(YEAR(TODAY()),12,31),"m")+1)+(O6:O12),--($A$6:$A$12=$R$2))
N6:N12	N6	=(B6/12+SUMIF(G6,1,$G$3)+SUMIF(H6:M6,1,$H$3:$M$3)/12)
O6:O12	O6	=SUMIF(C6:F6,1,$C$3:$F$3)

Cells with Data Validation
Cell	Allow	Criteria
R2	List	=$A$6:$A$12
R3	List	=$T$2:$T$13