Sum of range

[tkellymd]

I need to calculate the total amount due for a client. I have a range of cells that list the service provider (C9:E39), the amount of hours provided (B9), and the provider's hourly rate (Total Hours J6) which corresponds to the provider (Total Hours B6). Total hours B6 and J6 vary by provider but correlate for each provider.

I have figured out how to calculate the total to invoice per provider as:


=B9*'Total Hours'!J6*COUNTIF(C9:E39,'Total Hours'!B6)

but am unable to figure out how to use a sum or sumif function to calculate the total amount provided by all providers for the whole range (C9:E39) with out adding the above formula for each provider.

How can I reduce this to one formula?

 

[StephenCrump]

Sorry it's not clear to me even what your current formula is doing.

Can you please post a screenshot to illustrate?

 

[tkellymd]

The simplified version of the formula is:

= hours per shift *provider's hourly rate * total shifts per provider

 

[tkellymd]

https://8dbr5q.bn1301.livefilestore...K0fjpNmg?width=3200&height=1800&cropmode=none

 

[StephenCrump]

I'm guessing you want something like this:

F10:=COUNTIF(A$3:C$7,E10)*VLOOKUP(E10,E$2:F$6,2,)
F11: =SUMPRODUCT(F2:F6,COUNTIF(A3:C7,E2:E6))

If the number of hours per shift is always the same, as you've shown, then you could just multiply the result by the shift length in hours. Do you need to allow for variable length shifts?

Excel 2010
	A	B	C	D	E	F
1	Shifts					Rate
2	Place A	Place B	Place C		Bob	$15
3	Bob				Carol	$20
4		Carol			Ted	$12
5	Ted		Alice		Alice	$18
6		Ted			Bill	$16
7	Bob		Carol
8
9					Totals
10					Carol	$40
11					ALL	$112
1

If I'm on the wrong path please post screenshots, rather than pictures, of what your sample data looks like, and what results you're expecting to see.

Part B here gives you some options for posting screenshots:https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[tkellymd]

Book1
	A	B	C	D	E	F	G	H
6			MHHS			Methodist	St. Luke's	HCA
7	Day	Hours Worked	Intensivist	Intensivist	Intensivist	Intensivist	Intensivist	Intensivist
8	1.	10
9	2.	10
10	3.	10
11	4.	10
12	5.	10
13	6.	10
14	7.	10
15	8.	10
16	9.	10
17	10.	10
18	11.	10
19	12.	10
20	13.	10
21	14.	10
22	15.	10
23	16.	10
24	17.	10
25	18.	10
26	19.	10
27	20.	10
28	21.	10
29	22.	10
30	23.	10
31	24.	10
32	25.	10
33	26.	10
34	27.	10
35	28.	10
36	29.	10
37	30.	10
38	31.	10
39
40	Total Hours			0		0	0	0
41	Amount due
42
43			Rate/Hr
44				MHHS		Methodist	St. Luke's	HCA
45		General		$ 150.00
46		CV		$ 163.50
47		Blended		$ 180.00
OR Hours

Cell Formulas
Range	Formula
D40	=B38*COUNTIF(C8:C38,"*")
F40	=B38*COUNTIF(F8:F38,"*")
G40	=C38*COUNTIF(G8:G38,"*")
H40	=D38*COUNTIF(H8:H38,"*")

 

[tkellymd]

Book1
	B	C	D	E	F	G	H	I	J
2	Total Hours
3
4
5		ICU AM Hours	ICU PM Hours	OR Hours	Vacation Hours	Total Hours for Month	Critical Care Training	Administrative Hours	OR Hourly Rate
6	Kelly	161	54.5	0	0	216	Certified	0	$ 180.00
7	Khawaja	141	54	0	0	195	Certified	0
8	Tanauli	144	68	0	0	212	Certified	0	$ 180.00
9	Bell	165	27	0	0	192	Certified	0
10	Miller	74	40.5	0	0	114	Certified	0	$ 163.50
11	Deljoui	0	162	0	0	162	Certified	0
12	Faisal	0	0	0	0	0	Certified	0
13	Badar	26	0	0	0	26	Certified	0
14	Salcedo	34	0	0	0	34	Certified	0
15	Grewal	105	13.5	0	0	119	Certified	0
16	Contreras	74	13.5	0	0	87	Certified	0
17	Hammoudeh	60	0	0	0	60	Certified	0
18	Mutyala	74	0	0	0	74	Certified	0
19	Haider	0	0	0	0	0	Certified	0
20	Meeks	80	100	0	0	180	Certified	0
21	Zubieta	100	98	0	0	198	Certified	0
22	Retz	70	98	0	0	168	Certified	0
23	Agbo	60	140	0	0	200	Certified	0
24	Pulimamidi	170	32	0	0	202	Certified	0
Total Hours

Cell Formulas
Range	Formula
C6	=MHSW!L9+MHSW!N9+MHNE!H9+TRMC!H9
C7	=MHSW!L10+MHSW!N10+MHNE!H10+TRMC!H10
C8	=MHSW!L11+MHSW!N11+MHNE!H11+TRMC!H11
C9	=MHSW!L12+MHSW!N12+MHNE!H12+TRMC!H12
C10	=MHSW!L13+MHSW!N13+MHNE!H13+TRMC!H13
C11	=MHSW!L14+MHSW!N14+MHNE!H14+TRMC!H14
C12	=MHSW!L15+MHSW!N15+MHNE!H15+TRMC!H15
C13	=MHSW!L16+MHSW!N16+MHNE!H16+TRMC!H16
C14	=MHSW!L17+MHSW!N17+MHNE!H17+TRMC!H17
C15	=MHSW!L18+MHSW!N18+MHNE!H18+TRMC!H18
C16	=MHSW!L19+MHSW!N19+MHNE!H19+TRMC!H19
C17	=MHSW!L20+MHSW!N20+MHNE!H20+TRMC!H20
C18	=MHSW!L21+MHSW!N21+MHNE!H21+TRMC!H21
C19	=MHSW!L22+MHSW!N22+MHNE!H22+TRMC!H22
C20	=MHSW!L23+MHSW!N23+MHNE!H23+TRMC!H23
C21	=MHSW!L24+MHSW!N24+MHNE!H24+TRMC!H24
C22	=MHSW!L25+MHSW!N25+MHNE!H25+TRMC!H25
C23	=MHSW!L26+MHSW!N26+MHNE!H26+TRMC!H26
C24	=MHSW!L27+MHSW!N27+MHNE!H27+TRMC!H27
D6	=MHSW!M9+MHNE!I9+TRMC!I9+'Tele-ICU'!F7
D7	=MHSW!M10+MHNE!I10+TRMC!I10+'Tele-ICU'!F8
D8	=MHSW!M11+MHNE!I11+TRMC!I11+'Tele-ICU'!F9
D9	=MHSW!M12+MHNE!I12+TRMC!I12+'Tele-ICU'!F10
D10	=MHSW!M13+MHNE!I13+TRMC!I13+'Tele-ICU'!F11
D11	=MHSW!M14+MHNE!I14+TRMC!I14+'Tele-ICU'!F12
D12	=MHSW!M15+MHNE!I15+TRMC!I15+'Tele-ICU'!F13
D13	=MHSW!M16+MHNE!I16+TRMC!I16+'Tele-ICU'!F14
D14	=MHSW!M17+MHNE!I17+TRMC!I17+'Tele-ICU'!F15
D15	=MHSW!M18+MHNE!I18+TRMC!I18+'Tele-ICU'!F16
D16	=MHSW!M19+MHNE!I19+TRMC!I19+'Tele-ICU'!F17
D17	=MHSW!M20+MHNE!I20+TRMC!I20+'Tele-ICU'!F18
D18	=MHSW!M21+MHNE!I21+TRMC!I21+'Tele-ICU'!F19
D19	=MHSW!M22+MHNE!I22+TRMC!I22+'Tele-ICU'!F20
D20	=MHSW!M23+MHNE!I23+TRMC!I23+'Tele-ICU'!F21
D21	=MHSW!M24+MHNE!I24+TRMC!I24+'Tele-ICU'!F22
D22	=MHSW!M25+MHNE!I25+TRMC!I25+'Tele-ICU'!F23
D23	=MHSW!M26+MHNE!I26+TRMC!I26+'Tele-ICU'!F24
D24	=MHSW!M27+MHNE!I27+TRMC!I27+'Tele-ICU'!F25
E6	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B6)
E7	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B7)
E8	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B8)
E9	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B9)
E10	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B10)
E11	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B11)
E12	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B12)
E13	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B13)
E14	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B14)
E15	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B15)
E16	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B16)
E17	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B17)
E18	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B18)
E19	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B19)
E20	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B20)
E21	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B21)
E22	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B22)
E23	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B23)
E24	='OR Hours'!B38*COUNTIF('OR Hours'!C8:H38,B24)
J6	='OR Hours'!D47
J8	='OR Hours'!D47
J10	='OR Hours'!D46
G6	=SUM(C6:F6)+I6
G7	=SUM(C7:F7)+I7
G8	=SUM(C8:F8)+I8
G9	=SUM(C9:F9)
G10	=SUM(C10:F10)
G11	=SUM(C11:F11)
G12	=SUM(C12:F12)
G13	=SUM(C13:F13)
G14	=SUM(C14:F14)
G15	=SUM(C15:F15)
G16	=SUM(C16:F16)
G17	=SUM(C17:F17)
G18	=SUM(C18:F18)
G19	=SUM(C19:F19)
G20	=SUM(C20:F20)+I20
G21	=SUM(C21:F21)
G22	=SUM(C22:F22)
G23	=SUM(C23:F23)
G24	=SUM(C24:F24)

 

[tkellymd]

Your function =B8*(SUMPRODUCT('Total Hours'!J6:J24,COUNTIF(H8:H38,'Total Hours'!B6:B24))) worked perfectly. thank you very much.