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]

Thanks for that, how about

=LET(f,WRAPROWS(FILTER(AG8:DR8,($AG$6:$DR$6="Final Contribution")+($AG$6:$DR$6="Number of nights")+($AG$6:$DR$6="No. of fiscal year nights")),3),ff,IF(f="",0,f),SUM(INDEX(ff,,3)/INDEX(ff,,1)*INDEX(ff,,2)))

 

[Fluff]

You've added those values since you first linked the file.
Try

=LET(f,WRAPROWS(FILTER(AG9:DR9,($AG$6:$DR$6="Final Contribution")+($AG$6:$DR$6="Number of nights")+($AG$6:$DR$6="No. of fiscal year nights")),3),ff,IF(f="",0,f),SUM(IFERROR(INDEX(ff,,3)/INDEX(ff,,1),0)*INDEX(ff,,2)))

 

[Fluff]

My pleasure.

 

[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)))

 

[Fluff]

How about

=LET(f,WRAPROWS(FILTER(C8:GX8,($C$6:$GX$6="weekly true cost")+($C$6:$GX$6="No. of nights in fiscal year")),2),SUMPRODUCT(TAKE(f,,1)/7,TAKE(f,,-1)))

 

[SaraWitch]

Oh wow! I can normally read formulas (even if I can't write them!) but this is way over my head! It works perfectly, thank you so much.

However, I now need to do the same for the other total columns (and have just realised I need to add number of nights to the HB section). If I copy this formula and update the column headings, is there anything else I need to change? I think there is but don't understand what the text in red relate to:
=LET(f,WRAPROWS(FILTER(C8:GX8,($C$6:$GX$6="weekly true cost")+($C$6:$GX$6="No. of nights in fiscal year")),2),SUMPRODUCT(TAKE(f,,1)/7,TAKE(f,,-1)))

 

[Fluff]

The f is just a variable that holds the result of the wraprows/filter formula.
The ,2 is telling the wraprows function to to wrap the result of the filter into two columns.
The Take function takes the 1st column & divides by 7 & the multiplies that by the last column.

 

[SaraWitch]

Ah, that's grand, thank you, @Fluff.

However, because I have to do the same for the other total columns (and I've had to add number of nights to the HB section), it's now showing as #VALUE! (new link below). And the figure in HV9 should be £0.

EA Placements Manager

I still can't quite get my head around how your formula works in order for me to tweak it!

Just out of interest, why don't my original formulas in HU8 and HV8 work if there are blank cells when it's working in the test?

 

[Fluff]

That link is coming back as deleted.