countif not equal formula

Posted by itgirl168 on October 26, 2001 8:20 AM

I'd like to count g:g if f:f does NOT contain the word "New"..... If any other word appears in f:f, then I'd g:g to be counted

Thanks!

Posted by Juan Pablo on October 26, 2001 8:31 AM

How about

=COUNTA(G:G)-COUNTIF(F:F,"*New*")

Juan Pablo

Posted by itgirl168 on October 26, 2001 9:26 AM

Thanks! that worked! But now what if I want to count g:g if the cell contained a value of 2 AND f:f did not equal "New"?

Thanks!

How about

Posted by itgirl168 on October 26, 2001 9:30 AM

Thanks! that worked! But now what if I want to count g:g if the cell contained a value of 2 AND f:f did not equal "New"?

Thanks!

How about

Posted by itgirl168 on October 26, 2001 9:40 AM

I figured it out... Thanks!

Posted by Aladin Akyurek on October 26, 2001 9:58 AM

In that case, you need something more. Since you don't have definite ranges but just columns, you need either named dynamic ranges or an UDF by Grove. This UDF requires that you use it in the same worksheet where data are.

You need to add the UDF to your workbook by using Insert|Module after having activated Visual Basic Editor.

Function Used(r As Range) As Range
'
' Harlan Grove
'
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(Cells(1, 1), q))
End Function

Then use:

=SUMPRODUCT((USED(F:F)&LT;&GT;"New")*(USED(G:G)=2))

This does an exact match regarding "New".

Note. The UDF is needed because the functions that compute arrays cannot have whole columns as arguments.

Aladin

=======