Return Cell Address if Criteria Met?

[darknesseofmagyk]

Hello, friends! Wow...second post in one day. I'm so sorry! Any help would be greatly appreciated.

Let's say I have several thousand rows (in column 218) of text entries and know that 3 of those cells contains "Yes". I would like to know which cells have that value.

I've never used an IF function in Excel but have a nagging feeling that's what I should be doing. It would be something along the lines of:

=IF((all,0,218),"yes")RETURN(address)

--although I know there's no such command as "return" or "address", but you get my drift.

** Note ** "all" in the function above refers to a named range of rows.

 

[Jonmo1]

Generally, you don't need to return a cell's address to work with it..

What do you want to DO with the cells containing "yes" ?

Do you want to SUM the cells in another column based on which cells contain Yes in this column?

Perhaps you want

=SUMIF(INDEX(all,0,218),"yes",INDEX(all,0,219))

That will sum rows in the 219th column of all, where there is a "yes" in the 218th column.

 

[darknesseofmagyk]

Goodness knows, you're such a lifesaver!

I'm not really sure what I was hoping for--I wanted to know the exact row value, but you're right...that wouldn't typically be used in Excel, would it?

 

[Jonmo1]

You can get the row # of the first instance of yes like

=MATCH("yes",INDEX(all,0,218),0)

Though technically that is not the row#, it's the postion number within the array.
So if yes appears in row 12, but your named range (all) begins in row 5, then that match formula would return 8 because row 12 is the 8th row from 5


But what I'm getting at, is trying to get you to look a few steps ahead...
WHY do you want to know which cell(s) has "yes" in it?
What do you intend to do with it once you have that info?

 

[darknesseofmagyk]

Ahhh, that's a huge jump already--it returned the first instance of "yes", just as you said!

Regarding the 'why', I'm working with data separated by treatment time. Let's say (for named ranges):

$2:$730 = Initial
$731:$975 = 3-month
$975:$1099 = 6-month
$1100:$1156 = 12-month
$1157:$1449 = Biannual
$1450:$1990 = Completion

and obviously $2:$1990 would be All.

Somewhere in the 2:1990 range there are 3 cells with "Yes", and I'd like to know in which named range those three instances are. Are clients replying "Yes" in 6-month, 12-month, and biannual interviews? Are all 3 in completion interviews?

Alternatively, I'm wondering if there might be a way to return the named ranges in which those three cells are contained...but I'm thinking that's impossible...

 

[Jonmo1]

going back to your original formula in the other thread..

=COUNTIF(INDEX(all,0,218),"yes")

Just make 1 for each named range

=COUNTIF(INDEX(initial,0,218),"yes")
=COUNTIF(INDEX(3-month,0,218),"yes")
=COUNTIF(INDEX(6-month,0,218),"yes")
etc..

Then you will see which ones have the yes(s) in them

 

[darknesseofmagyk]

Yep! You're right--that's probably the best idea. Thank you so, so much! Again, my hero.