Excel formula/syntax question

[mkrass]

For the attached, I don't know how to write a formula for E17:E37 that will provide the same results as F17:F37. I think it has to do with how a merged cell is "considered", but I am not sure. Any help would be appreciated.

Approvals Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Today's Date	3/27/2024
2	Labor Threshold	10.00%
3	Revenue Threshold	60.00%
4	Credit Expiry	1/5/2024
5		1/6/2024
6		1/7/2024
7
8	Key
9	PTO/Holiday
10	Insert data
11	Insert data (weekend)		Note: Adjust Group or hide/unhide row/column, as needed DO NOT DELETE ROWs or COLUMNs
12	Approved - A
13	Planned - P							A
14								1/1/2024	1/2/2024	1/3/2024	1/4/2024	1/5/2024	1/6/2024	1/7/2024
15	Role	Resource Name	Country	Budgeted Hours	Billed Hours (Weekly Approval)	Billed Hours (Daily Approval)	Available Hours	A	A	A	A	A	A	A
16	Workstream 1			240	42	294	-162
17	Principle	Bob	US	60	2	14	18	2	2	2	2	2	2	2
18	Sr Con - NSX	Sally	US	80	2	14	62	2	2	2	2	2	2	2
19	Sr PM	Helen	US	100	2	14	82	2	2	2	2	2	2	2
20	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
21	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
22	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
23	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
24	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
25	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
26	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
27	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
28	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
29	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
30	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
31	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
32	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
33	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
34	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
35	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
36	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
37	<Insert Role Name>	<Insert Resource Name>	<Country>	0	2	14	-18	2	2	2	2	2	2	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A16,A17:C37	A16	='[DRAFT - TM Project Forecast - MCK Working File.xlsx]Project Setup'!B11
D16:G16	D16	=SUM(D17:D37)
E17:E37	E17	=SUMIF($13:$13,"A",17:17)
F17:F37	F17	=SUMIF($15:$15,"A",17:17)
G17:G37	G17	=D17-SUM(H17:AK17,AM17:BO17,BQ17:CU17,CX17:EA17,EC17:FG17,FI17:GL17,GN17:HR17,HT17:IX17,IZ17:KC17,KE17:LI17,LK17:MN17,MP17:NT17,NV17:OZ17,PB17:QC17,QE17:RI17,RK17:SN17,SP17:TT17,TV17:UY17,VA17:WE17,WG17:XK17,XM17:YP17,YR17:ZV17,ZX17:ABA17,ABC17:ACG17,ACI17:ADM17,ADO17:ADO17)
D19:D37	D19	='[DRAFT - TM Project Forecast - MCK Working File.xlsx]Project Setup'!F14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H17:N37,H39:N59	Expression	=COUNTIF($H$13, "A")	text	NO
H19:BQ19	Expression	=AND(ISNUMBER(H$14),SUMIFS($H19:H19,$H$14:H$14,"<>")>=$D$19*$B$2)	text	NO
H18:BQ18	Expression	=AND(ISNUMBER(H$14),SUMIFS($H18:H18,$H$14:H$14,"<>")>=$D$18*$B$2)	text	NO
H17:BS17	Expression	=AND(ISNUMBER(H$14),SUMIFS($H17:H17,$H$14:H$14,"<>")>=$D$17*$B$2)	text	NO
N17:N37,N39:N59	Expression	=COUNTIF($N$15, "A")	text	NO
M17:M37,M39:M59	Expression	=COUNTIF($M$15, "A")	text	NO
L17:L37,L39:L59	Expression	=COUNTIF($L$15, "A")	text	NO
K17:K37,K39:K59	Expression	=COUNTIF($K$15, "A")	text	NO
J17:J37,J39:J59	Expression	=COUNTIF($J$15, "A")	text	NO
I17:I37,I39:I59	Expression	=COUNTIF($I$15, "A")	text	NO
H17:H37,H39:H59	Expression	=COUNTIF($H$15, "A")	text	NO
H15:BS15	Cell Value	="A"	text	NO
H15:BS15	Cell Value	="P"	text	NO
H14:AL59	Expression	=H$14=$B$4	text	NO
H14:AL59	Expression	=H$14=$B$5	text	NO
H14:AL59	Expression	=H$14=$B$6	text	NO
H13,O13,V13,AC13,AJ13,AR13,AY13,BF13,BM13	Cell Value	="A"	text	NO
H13,O13,V13,AC13,AJ13,AR13,AY13,BF13,BM13	Cell Value	="P"	text	NO
G17:G37	Cell Value	<0	text	NO
G16:G59	Cell Value	<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H13:U13	List	A, P
H15:N15	List	A, P

 

[Jeffrey Mahoney]

I'm assuming your talking about Cell E17
=SUMIF($13:$13,"A",17:17)

Yes, Merged cells cannot apply to each individual column. The merged cell address is H13. You will have to put letters in each cell from H13:N13 for the formula to work as intended

 

[mkrass]

Jeffrey,
Thank you.
Is there a way to write a different formula that will allow for the merged cell and yet total the individual cells from the respective rows? Using the condition that the merged cell has a value of "A"?

 

[Michael M]

You might be better off Unmerging the cells, Highlight the cells to be used then using Format cells>>General>>Alignment>>"Center Across Selection"
Avoid Merged cells at all cost....IMHO,it should never have been in Excel in the first place

 

[mkrass]

You might be better off Unmerging the cells, Highlight the cells to be used then using Format cells>>General>>Alignment>>"Center Across Selection"
Avoid Merged cells at all cost....IMHO,it should never have been in Excel in the first place

Michael, thank you.

It appears that I won't be able to do what I would like. When I unmerge the 7 cells and apply your suggestion, visually, one still sees the seven cells. I wanted to give an option that would allow someone to approve labor for the entire week, via one choice, instead of making a selection for every day. It seems that I can provide the visual that I would like (via a merged cell), but, I don't know how to write a formula that can take the merged cell into consideration.