When you put the formula in the cell, hold down Ctrl and Shift keys and hit
Enter.

BUT look at Roger's solution ... it is simpler if it meets your need.

"Sarah (OGI)" wrote:

Thank you - I am going to try this now.
What does the -- mean in the formula? and what do you mean by 'it should be
entered with Ctrl+Shift+Enter?

(sorry if I'm asking daft questions!)

"Toppers" wrote:

I think you have overcomplicated this as SUMPRODUCT will return 0 if no match
found and SUM will handle blank cells so you should be able to remove the
ISERROR test.

Here is another version of your formula (if I have understood it correctly)
which should be entered with Ctrl+Shift+Enter:

=IF((SUMPRODUCT(--('PBH Update
Sheet'!$A$4:$A$499=$A11))*SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update
Sheet'!S$4:DM$4,0)))=0,"",SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update Sheet'!S$4:DM$4,0)))

HTH

"Sarah (OGI)" wrote:

(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum calculation
from another worksheet. I've used 'iserror' to show blank where no values
appear, i.e. for rows where the vlookup criteria does not appear, I want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be blank,
and because of this, there is no result because of the iserror, but I still
want the cells to be added and the result displayed despite there being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))