Sumproduct with blank cells Returning #Value. Help w/ excel attached

[hoops3335]

Hi,

I'm trying to take the monthly average of a table by using sum product, and dividing by total months. However, the sumproduct is returning a #VALUE error. Attached is the sheet.

MR Excel SumProduct Help.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
25					Annual	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly	Monthly
26	Year 1	11/1/2021	10/31/2022	12	$ 33.00	$ 68,750	$ -	$ 10,677	$ 208	$ 2,101	$ -	$ -	$ -	$ 2,575	$ 84,311
27	Year 2	11/1/2022	10/31/2023	12	$ 35.00	$ 72,917	$ -	$ 10,997	$ 840	$ 2,122	$ -	$ -	$ -	$ 2,575	$ 89,450
28	Year 3	11/1/2023	10/31/2024	12	$ 37.00	$ 77,083	$ -	$ 11,327	$ 1,490	$ 2,143	$ -	$ -	$ -	$ 2,575	$ 94,618
29	Year 4	11/1/2024	10/31/2025	12	$ 39.00	$ 81,250	$ -	$ 11,667	$ 2,159	$ 2,164	$ -	$ -	$ -	$ 2,575	$ 99,816
30	Year 5	11/1/2025	10/31/2026	12	$ 41.00	$ 85,417	$ -	$ 12,017	$ 2,849	$ 2,186	$ -	$ -	$ -	$ 2,575	$ 105,044
31	Year 5
32	Year 5
33	Year 5
34	Year 5
35	Year 5
36	Year 5
37	Year 5
38	Year 5
39	Year 5
40	Year 5
41	Year 5
42	Year 5
43	Monthly Average Costs			60		#VALUE!	$ -	$ 56,686	$ 7,546	$ 10,716	$ -	$ -	$ -	$ 12,875	$ 473,240
Charlie

Cell Formulas
Range	Formula
F25:O25	F25	=$P$25
E26:F42,H26:O42	F26	=IF($D26="","",IF(F$25=$T$53,F88/$D26,IF(F$25=$T$52,F88,"CHECK")))
G26:G42	G26	=IFERROR(IF($D26="","",IF(G$25=$T$53,G88/$B$18,IF(G$25=$T$52,G88,"CHECK"))),0)
F43	F43	=IF($P$25=$T$52,SUM(F26:F42),IF($P$25=$T$53,SUMPRODUCT(NOT(ISBLANK($D26:$D42)),$D26:$D42,F26:F43)/$D$43,"CHECK"))
D43,G43:O43	G43	=SUM(G26:G42)
B26	B26	=B21
C26	C26	=EOMONTH(B26-1,D26)
B27:B42	B27	=IF(D27<1,"",EDATE(B26,D26))
C27:C42	C27	=IF(B27="","",EOMONTH(B27-1,D27))
A26:A42	A26	=ROUNDUP(SUM($D$26:D26)/12,0)
A43	A43	=IF($P$25=$T$52, "Total Costs",IF($P$25=$T$53,"Monthly Average Costs", "CHECK"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A26:O42	Expression	=$D26=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E25:P25	List	=$T$52:$T$55

 

[hoops3335]

So essentially taking SumProduct of the $ amount in column F with the months in column D, divided by the total months in cell d43. Thus, returning the monthly average costs. I would then extrapolate the equation for columns G through O, holding constant the Column D and Cell D43 portions of the equation.

 

[Fluff]

You have a circular reference in F43

 

[hoops3335]

Yikes had to slap my head on that one. I'll blame it on lack of sleep... All work, no play makes Jack a dull boy. Thanks, and sorry for being an idiot - I should have caught that one.

 

[Fluff]

Not a problem, we've all been there. Sometimes it just needs an extra set of ?