Ask Your Question

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

asked 2021-04-12 23:26:17 +0200

stevetalley gravatar image

updated 2021-04-13 22:28:35 +0200

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 flag offensive 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),

Please do not use Add Answer but edit your original question to enhance the details of your question and/or to upload sample file(s) (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

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

1 Answer

Sort by » oldest newest most voted

answered 2021-04-13 08:31:32 +0200

keme gravatar image

updated 2021-04-14 07:25:22 +0200

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

edit flag offensive delete link more



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

Earnest Al gravatar imageEarnest Al ( 2021-04-14 00:00:58 +0200 )edit

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

Earnest Al gravatar imageEarnest Al ( 2021-04-14 00:37:12 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-04-12 23:26:17 +0200

Seen: 54 times

Last updated: Apr 14