sorry threr is some mistake in the macro I have given . ignore the message

"R..VENKATARAMAN" wrote in message news:...
try this though inelegant macro

Public Sub test()
Dim i As Integer
i = 0
Dim myrange As Range
Dim c As Range
Set myrange = Range(Range("a1"), Range("a1").End(xlDown))
For Each c In myrange
If c = "FAC" And c.Offset(0, 1) = "1" _
And c.Offset(0, 2) = "SL" _
Then i = i + 1
Next
For Each c In myrange
If c.Offset(0, 2) = "SL" Then i = i + 1
Next
MsgBox i
End Sub


"jaypee" wrote in message
...
good day!

i need a excel formula that counts FAC, 1,SL or VL as 1.

FAC 1 SL
FAC 1 SL
FAC 2 SL
FAC 1 VL
FIS 1 VL

in this sample the formula must count 3..

thanks

I assume you are trying to do a countif with 3 criteria and you have 3
columns of data you are checking. So you want to know if FAC is in
column 1, 1 is in column 2, and either SL or VL is in column 3. The
way to do a 3-criteria count is to use an array formula. Assuming we
are using columns A, B, and C this would be your formula:


=SUM(IF((A1:A5="FAC")*(B1:B5=1)*(C1:C5={"SL","VL"} ),1,0))

This is an array formula, so you will have to press ctrl+shift+enter
instead of just enter when you finish the formula. If you do it right
you should get brackets around the formula.

You can change the ranges to meet your needs.