Help required to create a formula (1 Viewer)

[smiler44]

I am not sure if one formula will do this or of a I need a formula in each row and then total the result. If a formula cant do this then I will resort to a macro.

column B contains 0, 1, 2 and 3
column E contains the estimated value
column F contains the real value

I want to know on a row by row basis, how many times the value of F, is less then the value of E, when column B contains a 1

can you please help as I cannot think how to do it

Thank you

smiler44

 

[plog]

=IF(AND(B1=1, F1<E1), 1,0)

 

[arnelgp]

same but shorter:

=AND(B1=1,F1<E1)*1

 

[smiler44]

thank you both, for some reason I did not get a email notification.
I think I will need to put "that" formula in a cell in each row, is that right?
Is there a way to put a formula in one cell and it do all the work, there could be 800 rows?

smiler44

 

[arnelgp]

you don't need to type it on all cells.
after entering the formula, Double-Click on the small
square at the bottom of that cell, to "fill" down the
formula.

 

[plog]

So to do it using just 1 cell you would write a custom function. This should do it:

Function count_ValidRows()
' determines how many rows

Dim ws_Page As Worksheet        ' reference to sheet
Dim rw As Range                 ' range of cells to check
Dim ret As Integer              ' return value, valid rows

ret = 0

Set ws_Page = ActiveSheet
For Each rw In ws_Page.Rows
' loops through each row checking them one by one

  If ws_Page.Cells(rw.Row, 1).Value = "" Then
' first blank value in column A makes function exit
    Exit For
  End If

  If (ws_Page.Cells(rw.Row, 2).Value = 1) And (ws_Page.Cells(rw.Row, 5).Value > ws_Page.Cells(rw.Row, 6)) Then
  ' B column =1 and E column > F column is valid
    ret = ret + 1
    End If

Next rw

count_ValidRows = ret

End Function

Paste that into a module and then put this in a cell on the sheet you want to total:

=count_ValidRows()

That cell will should show your answer. I suggest you double check it by using the expression given above and summing those to make sure it matches what count_ValidRows() produces.

 

[arnelgp]

you can put this on a any cell:

=SUMPRODUCT((B:B=1)*(F:F<E:E))