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]

COUNT is not sum. The reason your SUMPRODUCT is failing is nothing to do with the LAMBDA. You have something non-numeric in the Y5:BW5 cells and you are trying to use those in a multiplication, hence the #VALUE! error.

 

[robcooper2001]

BTW...
The formula in O5 is
=SUMPRODUCT($Y$5:$BW$5*(YEAR($Y$4:$BW$4)=O$4))
Maybe this is written incorrectly!?! Wood for the trees maybe!!

 

[RoryA]

Try using:

=SUMPRODUCT($Y$5:$BW$5,--(YEAR($Y$4:$BW$4)=O$4))

 

[robcooper2001]

Sh!t! Was I supposed to pick something up from that? I missed it whatever it was... Any clues for me?

 

[RoryA]

Did you try it?

 

[robcooper2001]

GENIUS... Very much thanks....
Can you explain it to me in simples terms, please? I need to understand.... What does the double dash do?

 

[RoryA]

This part returns an array of True/False values:

(YEAR($Y$4:$BW$4)=O$4)

and the -- then coerces those into 1s and 0s. The key is using a comma separator in SUMPRODUCT rather than trying to multiply the values in Y5:BW5, since that will fail if there is text in any of those cells. SUMPRODUCT will simply ignore text in any range passed to it.

 

[robcooper2001]

Aaaah! Gooogly...
True / false 1 / 0 .... very clever....