Formula not calculating if blank cells

[SaraWitch]

Hello peeps,

I am at an impasse with this and am hoping someone can help.

I have a formula that is based on multiple cells. This is working if there is a value in every relevant cell. However, I need it to work even if there are blanks. I have replicated in a test and this is working fine - just not the formula in the main area of the worksheet.

It would be too lengthy to list the issue details here (and besides, on paper, it works!), so, I have attached a link to the actual spreadsheet.

EA Placements Manager

The problem is on worksheet 'EA Placements' and the column in which the formula is not behaving is GY (from row 8). I have laid out a test on columns GX - HK, row 1.

Fresh eyes and any help would be very much appreciated

 

[Fluff]

What is the actual calculation that needs to be done?

 

[SaraWitch]

"HB Amount Awarded" (weekly figure) plus the "HB Fuel Deduction" (weekly figure) divided by 7 (to get a daily rate) multiplied by the "No. of nights in fiscal year". To be done for multiple claims across columns (I had updated the column letters by the way (just hadn't included it in my amended code)).

Hope this makes sense...

 

[Fluff]

Ok, try

=LET(f,WRAPROWS(FILTER(DC8:FE8,($DC$6:$FE$6="HB Amount Awarded")+($DC$6:$FE$6="HB Fuel Deduction")+($DC$6:$FE$6="No. of nights in fiscal year")),3),ff,IF(f="",0,f),SUMPRODUCT((TAKE(ff,,1)+TAKE(ff,,2))/7,TAKE(ff,,-1)))

 

[SaraWitch]

Hmmm... that's giving me a #VALUE (columns have changed somewhat since last time, but formula is in Column OE...)

EA Placement Manager

 

[Fluff]

Ok, how about

=LET(f,WRAPROWS(FILTER(HK8:NN8,($HK$6:$NN$6="HB Amount Awarded")+($HK$6:$NN$6="HB Fuel Deduction")+($HK$6:$NN$6="No. of fiscal year nights")),3),ff,IF(f="",0,f),SUMPRODUCT((TAKE(ff,,1)+INDEX(ff,,2))/7,TAKE(ff,,-1)))

 

[SaraWitch]

You beaut!! Thank you so much, @Fluff!

 

[Fluff]

My pleasure.