Excel Lambda Function returns #VALUE and will not SUMPRODUCT

[robcooper2001]

I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in there and it just won't have it... Why ???

Row 5 in blue are figures as a result of the lambda function based on a contract value broken into 2 parts for design and construction based on a % between 2 dates from initial start to end from cols Q - X not really relevant. the lambda takes teh result and spreads in across the months (col y onwards). Works fine but the annual sum columns N - P are summing fixed months in the spread (y onwards) and if the date changes in y4 the calcs are all wrong. So to make it dynamic in terms of teh start date in Y4 columns N - P mut look at the years in row4 (y onwards) and add up the ones for the year. But change the spread calc Y5 onwards to speed up the sheet and the SUMPRODUCT does not work and returns #VALUE... Can anyone help me on this please? I would post the sheet but it's a bit confidential.

Y4 formula: =SPRDVAL(Y$4,$Q5,Z$4,$W5,$S5,$X5,$U5)

Lambda for SPRDVAL:

=LAMBDA(DATEP,OPD,DATEF,VWOF,SOS,VWON,COMD,IFERROR(IFS(AND(MONTH(DATEP)=MONTH(OPD),YEAR(DATEP)=YEAR(OPD)),(DATEF-OPD)*VWOF,AND(MONTH(SOS)=MONTH(DATEP),YEAR(SOS)=YEAR(DATEP)),(SOS-DATEP)*VWOF+(DATEF-SOS)*VWON,AND(MONTH(COMD)=MONTH(DATEP),YEAR(COMD)=YEAR(DATEP)),(COMD-DATEP)*VWON,AND(OPD<DATEP,SOS>DATEP),DAY(EOMONTH(DATEP,0))*VWOF,AND(SOS<DATEP,COMD>DATEP),DAY(EOMONTH(DATEP,0))*VWON,TRUE,""),""))

I have broken each of the parts of the formula in O5 and all work (inc SUMPRODUCT of row 5 spread) but once they are combined... no joy.. Just a #VALUE

 

[RoryA]

Welcome back to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Lambda Function returns #VALUE and will not SUMPRODUCT

I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in ...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[robcooper2001]

Welcome back to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Lambda Function returns #VALUE and will not SUMPRODUCT

I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in ...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies Rory. Newbee..!

 

[robcooper2001]

Crossposting link:

Excel Lambda Function returns #VALUE and will not SUMPRODUCT

I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in ...

stackoverflow.com

 

[robcooper2001]

At least it won't come up on the unanswered thread list.

 

[RoryA]

It's hard to debug that out of context. Are all the cells in Y4:CC4 populated with dates? Are all the cells in Y5:CC5 actually empty as they appear or are there formulas in there?

 

[robcooper2001]

It's hard to debug that out of context. Are all the cells in Y4:CC4 populated with dates? Are all the cells in Y5:CC5 actually empty as they appear or are there formulas in there?

The dates only go up to BW now. I tried with a formula of =EDATE($Y$4,Y$3) to make a sequence and just dates but neither affected it really.
I also used a helper in row 1 =YEAR(Y4:BW4) and referenced that but to no avail.
I tried a helper in N1 =YEAR(N4) and referenced that.
And a test helper in O1 =SUM(Y5:BW5).
None of these worked. This whole sheet is a duplicate of another sheet but in the spread (Y5:BW175) the lambda function was undone (bearing in mind a few address changes due to added row/columns) to:
=IF(AND(MONTH(AA$3)=MONTH($R4),YEAR(AA$3)=YEAR($R4)),(AB$3-$R4)*$X4,IF(AND(MONTH($T4)=MONTH(AA$3),YEAR($T4)=YEAR(AA$3)),($T4-AA$3)*$X4+(AB$3-$T4)*$Y4,IF(AND(MONTH($V4)=MONTH(AA$3),YEAR($V4)=YEAR(AA$3)),($V4-AA$3)*$Y4,IF(AND($R4<AA$3,$T4>AA$3),DAY(EOMONTH(AA$3,0))*$X4,IF(AND($T4<AA$3,$V4>AA$3),DAY(EOMONTH(AA$3,0))*$Y4,0)))))

Testing it every possible way in both sheets, without the lambda function =SUMPRODUCT($AA4:$BP4*(YEAR($AA$3:$BP$3)=N$3)) worked fine... (again minor cell address changes).
I can send the sheet but not post it due to confidentiality.

 

[robcooper2001]

I also ensured all the formats are the same in both sheets.

 

[RoryA]

Did =YEAR(Y4:BW4) return any errors?
=SUM(Y5:BW5) doesn't really tell you anything since SUM will ignore text. Do =COUNTA(Y5:Bw5) and =COUNT(Y5:Bw5) return the same value?

 

[robcooper2001]

=YEAR(Y4:BW4) worked fine along the whole row.
I used SUM just to see if the Lambda would add up. Which it seemed to.
But =COUNTA(Y5:Bw5) returns £51 and =COUNT(Y5:BW5) returns £11 ......! Neither of which are £432020!!