Help writing excel formula?

You cannot use a formula to list the rows containing the specified value. You could use a formula in a 'helper' column to display 'Yes' if C:I contained a specified value in that row. You still would have to scroll down to identify the rows.

Here is a different approach using a VBA macro. The following macro, when called, will request user input to enter a value to search for. After the user enters a number, a message box will display advising how many rows contain the value and list the row numbers in column X. If you are using column X, change the "X" references in lines 4, 9, 13, and 22 to a column letter you wish to display the result in.

Copy the macro to the clipboard:

Sub List_Rows()

Dim i, LastRow, rowList, ctr

LastRow = Range("C" & Rows.Count).End(xlUp).Row

Columns("X:X").ClearContents

setCrit = InputBox("Please enter the number to audit.", "> Or =")

If setCrit = "" Then

Exit Sub

End If

Range("X1").Value = "Rows >= " & setCrit

For i = 1 To LastRow

For j = 3 To 9

If Cells(i, j).Value >= Val(setCrit) Then

Range("X" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, "C").Row

ctr = ctr + 1

GoTo here

End If

Next j

here:

Next i

If ctr > 0 Then

MsgBox ctr & " rows contain a number greater than or equal to " & setCrit _

& ". " & Chr(10) & Chr(10) & "The row numbers are listed in Column X.", _

vbOKOnly, "Results for " & setCrit

Else

MsgBox "No rows found containing a value greater than or equal to " & _

setCrit, vbOKOnly, "Not Found"

End If

End Sub

Press ALT + F11

In the menus at the top of the VBE, select INSERT > MODULE

Paste the macro into the module editing area to the right.

Close the VBE and return to Excel.

Press ALT + F8

When the Macros window opens, highlight the macro and click 'Options..'.

Enter a letter to be used as a keyboard shortcut and click 'OK'.

Close the Macros window.

Press CTRL + your shortcut letter whenever you wish to call the macro.

Use IF() function, and as the logical test employ the AND() function:

=IF(AND(C1=31; D1 = 31; E1 = 31; F1 = 31; G1 = 31; H1 = 31; I1=31); "yes"; "no")

You would write this function in your 22nd column, and fill the rest of the rows.

The second formula is pretty much the same:

=IF(AND(C1>=27; D1>=27; E1>=27; F1>=27; G1>=27; H1>=27; I1>=27); "yes"; "no")

Edit: I forgot the alphabet :p

In J1 enter =IF(SUM(C1:I1)=317,ROW(),"")

In K1 enter =IF(OR(C1>=27,D1>=27,E1>=27, F1>=27,G1>=27,H1>=27,I1>=27), ROW(),""))

In L1 enter =SMALL(J:J,ROW()) gives rows with all cells 31

In M1 enter =SMALL(K:K,ROW()) gives rows with any cell >=27

Copy the 4 cells down list, L and M until NUM appears. If these formula are placed in row 2 due to headers amend Row() to read Row()-1

Are the numbers to the right of the / character always one digit in length? If so, paste this formula in D16 =SUMPRODUCT((N(RIGHT(A1:A1000, 1)=RIGHT(D15,1)))*(B1:B1000)) If your data goes past row 1000, change the 1000's to some number larger than what you have. The above formula assumes all your data in column A starts with g/z.