#VALUE!

[bfuentes1412]

Hello,
I'm trying to sum values from 4 cells, G17, H17, I21, & I23, in I25 however, two cells, G17 & H17, need to be divided by .9. I have set all totals in row 17, along with I21 & I23 to "" when value is zero or displays an error b/c of zero value. When attempting to sum the values, if any cell is empty/blank I receive the "#VALUE!" error and it won't show any total sums. I've tried a combination of =IF(ISERROR), IF(SUM) but no joy. If ANY of the cells are blank it won't show the total. I know setting the total values to "" is the culprit but I'm sure there's a way to work around this. In the example below, the total should actually be 147.89

All in One Calculator v2.xlsm
	C	D	E	F	G	H	I	J	K	L
6
7	CLIMATE ZONE		3, 5, 7
8					(KW)
9	UNIT /	DESIGN		Ev	HSE	Addt'l	# of	KVA per	EV
10	BLDG	Sq.Ft	# of Cust.	Included	Load	Load	EV	Charger	Load
11	1	1200	22	YES	22	22
12	2
13	3
14	4
15	5
16
17	TOTAL	1200	22	YES	22	22
18		(Avg.)			(KW)	(KW)		(Avg.)	(KVA)
19
20
21	DESIGN SQ.FT		1200		MAX DWELLING		99.00	(KVA)
22
23	INITIAL XFMR SIZE		150		EV LOAD			(KVA)
24
25	MAX # OF CUST.		40		TOTAL LOAD		#VALUE!	(KVA)
26
27	KVA PER CUST.		4.50		RECOMMENDED
28					XFMR SIZE
29
Calculator

Cell Formulas
Range	Formula
K11:K15	K11	=IF(SUM(I11)*(J11)=0,"",SUM(I11)*(J11))
D17	D17	=IFERROR(SUM(D11:D15/(COUNTA(D11:D15))),"")
E17,G17:I17,K17	E17	=IF(SUM(E11:E15)=0,"",SUM(E11:E15))
F17	F17	=IF(COUNTA($F$11:$F$15),IF(COUNTIF($F$11:$F$15,"YES"),"YES","NO"),"")
J17	J17	=IF(SUM(J11:J15)=0,"",SUM(J11:J15/(COUNTA(J11:J15))))
E21	E21	=IF(""=D17,"",INDEX(AY4:AY14,MATCH(1,--(AY4:AY14>=D17),0)))
I21	I21	=IFERROR((E27*E17),"")
E23	E23	=IFERROR(IF(E7<9,INDEX(FILTER(AQ4:AQ91,(AO4:AO91=F17)*(AP4:AP91=E21)*(AR4:AR91>=E17),"0"),1),INDEX(FILTER(AJ4:AJ91,(AH4:AH91=F17)*(AI4:AI91=E21)*(AK4:AK91>=E17)),1)),"")
I23	I23	=K17
E25	E25	=IFERROR(IF(E7<9,INDEX(AR4:AR91,MATCH(1,(AP4:AP91=E21)*(AO4:AO91=F17)*(AQ4:AQ91=E23),0)),INDEX(AK4:AK91,MATCH(1,(AI4:AI91=E21)*(AH4:AH91=F17)*(AJ4:AJ91=E23),0))),"")
I25	I25	=SUM((G17:H17)/0.9+I21+I23)
E27	E27	=IFERROR((E23*1.2)/E25,"")
I27	I27	=IFERROR(IF(0=I25,"",INDEX(AU4:AU9,MATCH(1,--(AV4:AV9>=I25),0))),"")

Cells with Data Validation
Cell	Allow	Criteria
D11:D15	List	=$AY$4:$AY$14
E7	List	=TABLES!$U$4:$U$6
F11:F15	List	=TABLES!$U$8:$U$9

 

[Sufiyan97]

Try changing formula to

=SUM((G17:H17)/0.9+SUM(I21,I23)

 

[bfuentes1412]

Thanks @Sufiyan97. That didn't work; I still received the #VALUE! error if G17 & H17 were blank and wouldn't add any of the other cells, but gave me an idea to try this, which worked! Thanks again for the help! Much appreciated.

=IF(SUM(G17)/0.9+SUM(H17)/0.9+SUM(I21)+SUM(I23)

 

[Sufiyan97]

Glad you sorted it and
Glad I could help at some extent.

 

[Joe4]

Thanks @Sufiyan97. That didn't work; I still received the #VALUE! error if G17 & H17 were blank and wouldn't add any of the other cells, but gave me an idea to try this, which worked! Thanks again for the help! Much appreciated.

=IF(SUM(G17)/0.9+SUM(H17)/0.9+SUM(I21)+SUM(I23)

Sounds like you have some non-numeric entries in there messing things up.
That is the only explanation why adding SUM in front of those single range values would make a difference.

 

[Peter_SSs]

.. but gave me an idea to try this, which worked!

=IF(SUM(G17)/0.9+SUM(H17)/0.9+SUM(I21)+SUM(I23)

I don't think so since that isn't a valid formula as posted, so I have removed the 'Mark as solution'
Perhaps you meant just this?

=SUM(G17)/0.9+SUM(H17)/0.9+SUM(I21)+SUM(I23)

A shorter alternative would be

=(N(G17)+N(H17))/0.9+N(I21)+N(I23)

 

[bfuentes1412]

Sorry @Peter_SSs. You are correct. I must have accidentally added the "IF" and copied prior. The formula I was using, which worked, didn't include that. Thanks for that catch and providing the alternate, shorter solution.