CODEWITHSUNDEEP
excelexcel-formula
GT.
GT.

Reputation: 792

Display the value from the formula ONLY if another cell has the value that I want

I've devised a formula that I want my cells to have, but I only want the value to show up if ANOTHER cell is populated with the value that I want.

My current formula is below:

=COUNTIFS($R$3:R21, "Brain")

However, this formula doesn't check whether or not the other cell has the value that I want to check for.

I put together a formula that I thought would check for whether or not the cell is populated:

=COUNTIFS($R$3:R21, "Brain", R21, "Brain")

Unfortunately, this formula doesn't work (it returns #VALUE!). I think it fails because R21 is already included in the COUNTIFS.

I also tried this similar formula, which also failed to work: 

=COUNTIFS($R$3:R21, "Brain", R21:R21, "Brain")

I looked online and I found this possible solution:

=IF(ISNUMBER(SEARCH("Brain",R21)),"COUNTIFS($R$3:R21, 'Brain')","")

Unfortunately, this formula displays the text of the formula I want, and not the actual value of the formula.

Does anyone know how I could display the value from the formula ONLY if the cell I'm checking has the value that I want? Thanks.

Upvotes: 1

Views: 580

Answers (1)

user8521945
user8521945

Reputation:

Try

=IF(ISNUMBER(SEARCH("Brain",R21)),COUNTIFS($R$3:R21, 'Brain'),"")

Upvotes: 0

Related Questions