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]

Your Nightly Cost headings have a line feed rather than a space, try

=LET(f,WRAPROWS(FILTER(AC8:CO8,($AC$6:$CO$6="Nightly"&CHAR(10)&"Cost")+($AC$6:$CO$6="No. of nights in fiscal year")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

 

[SaraWitch]

I offer deepest, sincerest thanks, @Fluff. This thread has truly blown my mind and enhances my learning via the Greats such as you! That's a bit gushy for a Thursday evening, but heartfelt!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[SaraWitch]

Hi there,

I just need to tweak one of these formulas and am getting into a muddle.

The existing formula, which works by multiplying a nightly amount by the number of fiscal nights is:

=LET(f,WRAPROWS(FILTER(AG8:DR8,($AG$6:$DR$6="Nightly Contribution")+($AG$6:$DR$6="No. of fiscal year nights")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

However, a total amount will now be entered, rather than a nightly one, so I need this total figure divided by the "Number of Nights" and then multiplied by the "No. of fiscal year nights". I have tried, among others and without success:

=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")),2),ff,IF(f="",0,f),SUMPRODUCT(TAKE(ff,,1),TAKE(ff,,-1)))

Any advice would be appreciated

 

[Fluff]

Can you post some sample data.

 

[SaraWitch]

Of course:
EA Placements Manager

Formula in question is in JB8 (I intend to replicate it for JC8 ("Service Charge Paid"))...

 

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

 

[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