Resolve value area on a SUM, IF and NETWORKDAYS.INTL formula combination returning a value error.

[jessitarexcel]

May I ask why I am getting a value error? I have checked the column formats and they are all as they should be, dates for dates, number for numbers and text for text. I think I am just too tired today to see something simple and thought I'd post in case anyone else out there was having the same issue.

Referred to Date	Referred on Date	Initial Case closure (date)	Does Application have initial closure date?	Closed Application Timeframes
Date	Date	Date	=IF([@[Initial Case closure (date)]]="","In Progress","Closed")	=SUM(IF([@[Does Application have initial closure date?]]="Closed",(NETWORKDAYS.INTL([@[Referred to PIU Date]],[@[Initial Case closure (date)]],1,('Public Holiday List'!B2:B11))),"In Progress"))

This is the formula that I am using that is having the value error. If the value in Does Application have initial case closure date is closed, it shows the value, but if it is In Progress, it shows a value error. I have tried changing "In Progress" in the formula below to another text reference, but it doesn't work. I thought there may be a circular error issue.

=SUM(IF([@[Does Application have initial closure date?]]="Closed",(NETWORKDAYS.INTL([@[Referred to PIU Date]],[@[Initial Case closure (date)]],1,('Public Holiday List'!B2:B11))),"In Progress"))

This is an example of the results below. None of this is real data. It is just test data to check the formulas before implementation.

Referred to PIU Date	Referred on Date	Initial Case closure (date)	Does Application have initial closure date?	Total Time between Referred to PIU Date and Initial Case Closure (All Days)	Total Time between Referred to PIU Date and Initial Case Closure (Work Days including Public Holidays)	Closed Application Timeframes
1/02/2023	15/03/2023	15/04/2023	Closed	73	51	51
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
15/01/2023			In Progress	-44941	-32100	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
			In Progress	0	0	#VALUE!
28/02/2023			In Progress	-44985	-32132	#VALUE!

 

[Anthony47]

You get the error because when the IF resolves as False you excecute a SUM("In Progress"), that is nonsense. If you remove SUM from the formula you get some meaningful results; but you said nothing about the expected result so I can't suggest anithing else

 

[jessitarexcel]

Please disregard this entire post. It was a draft that wasn't intended to be posted. The answer is to remove the sum from the formula and it works as intended.