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

 

[SaraWitch]

Absolutely spot-on!! I would never have got there on my own - thank you so much @Fluff. And... ...this has really helped me understand the formula better, so double-thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[SaraWitch]

Oops! Just copied the formula to other cells and am getting a #DIV/0 error - any idea why?

EA Placements Manager

 

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

 

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