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

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

jaypee -

Have you considered putting some field names at the top so that it's an
Excel database/list and then using a Pivot Table to do the counting?

(I don't understand your test data. The SL/VL seems to be irrelevant.)

- Mike
www.mikemiddleton.com

"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

Although the array-entered formula suggested by someone else works, I
try to avoid them whenever possible. In many cases, they can be
replaced with a similar SUMPRODUCT() formula. For example:

=SUMPRODUCT(
--(A1:A5="FAC"),
--(B1:B5=1),
--(C1:C5="SL")--(C1:C5="VL"))