# In a Named Range, how do I look for a "Y"?

I have a named range, the contents of which is either a "Y" or it is blank. I want to find all the blank cells and add a number in an adjacent cell to a sum in a different cell. Seems too complicated to me. Is it possible?C:\fakepath\Banking Test.ods

edit retag close merge delete

Could you please elaborate by an simple sample file

• The data you have
• What you want to add, where based on which condition
• How your result should look like

(In fact, I don't understand your current description of the task to be performed),

( 2021-04-12 23:41:58 +0200 )edit

Sort by » oldest newest most voted

SUMIF() should do that.

You need to check for the non-existence of a "Y", which you may solve by a criterion given as text. To insert a quote character into a quoted string, you type the character twice.

Something like =SUMIF('named range';"<>Y";'summation range')

For summation range, you may also use the OFFSET() function. For an adjacent column, the horizontal offset would be +1 or -1.

Something like =SUMIF('named range';"<>Y";OFFSET('named range';0;1))

Edit: removed quotes which broke the condition. Thanks @Earnest Al

more

1

Following @keme solution applied to your sample =SUMIF(Rec,"<>Y",OFFSET(Rec,0,-1)).

( 2021-04-14 00:00:58 +0200 )edit
1

Whoops. For Locale independent =SUMIF(Rec;"<>Y";OFFSET(Rec;0;-1))

( 2021-04-14 00:37:12 +0200 )edit