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]

How about

=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),IFERROR(SUM(INDEX(ff,,3)/INDEX(ff,,1)*INDEX(ff,,2)),0))

 

[SaraWitch]

Hmmm. That's returning zero for everything now...

 

[Fluff]

What should it return?

 

[SaraWitch]

I've added notes to C.JB and C.JK:

JB9 should be £99.53
JB10 should be £98.84
JB11 should be £20.00
etc.

And:

JK9 should be £100.00
JK9 should be £100.00
JK9 should be £20.00
etc.

It's odd because it's working in the first row (JB8 and JK8)...

 

[Fluff]

But there is no Final contribution for row 10

 

[SaraWitch]

There should be :

 

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

 

[SaraWitch]

Sorry, @Fluff! I'd updated in post #22 - I should have let you know that I had

And that formula did the trick! Superstar! Thank you so much...

 

[Fluff]

My pleasure.

 

[SaraWitch]

Hi @Fluff.

I need to tweak one of the formulas as another column has been added, the totals of which need to be included. I have tried a couple of variations, but not having much luck.

Original formula:

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

I now need to add both "HB Amount Awarded" and the new column "HB Fuel Deduction". I have tried, without joy:

=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)/7,TAKE(ff,,-1)))

Am I right in thinking I have to amend the TAKE function because I now have 3 columns?
Any help would be appreciated