[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

Find Value in a Row in Excel

Posted on 2015-01-12
Medium Priority
64 Views
I have a spreadsheet and need to search a range in each row looking for a value (0). Example: Look in F12:P12 and if a 0 is found in any cell return 0 Else 1.
0
Question by:EMCIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 24

Expert Comment

ID: 40544387
``````Sub DoIt()
For Each cel In Selection
If cel.Value = "0" Then
Exit For
Else
End If
Next
End Sub
``````
0

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 40544389
=IF(COUNTIF(F12:P12,0),0,1)
should do it.
0

LVL 11

Author Comment

ID: 40544409
=IF(COUNTIF(F12:P12,0),0,1) works great. I have another column that needs to search specific columns as well.
Example: G12, I12, J12, M12, N12 only but when I try to string the cells it doesn't work.
0

LVL 11

Author Comment

ID: 40544441
I got it!

=IF(SUM(G22,I22,J22,M22,N22)=COUNT(G22,I22,J22,M22,N22),1,0)

0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll