Counting rows with criteria
 

Hello,

I've been looking for a solution to this problem, I need to get a count
of all rows that have an "X", but not the total number of "X" in the
range. Any solutions or tips? Thanks!

A B C D
1 / / / /
2 X X X X
3 / / / /
4 / / X /
5 X X X X
6 / X / /
7 X X X X
8 X X X X
9 / / / X
10 / / X X
11 / / / /
12 / / / /
13 / / / /
14 / / / X
15 / / / /
16 X X X X
17 / X / /
18 / / / /
19 / / / /
20 / / X /

TKO

Counting rows with criteria
 

=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A25)-1,,1,2),"*X*")0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
ups.com...
Hello,

I've been looking for a solution to this problem, I need to get a count
of all rows that have an "X", but not the total number of "X" in the
range. Any solutions or tips? Thanks!

A B C D
1 / / / /
2 X X X X
3 / / / /
4 / / X /
5 X X X X
6 / X / /
7 X X X X
8 X X X X
9 / / / X
10 / / X X
11 / / / /
12 / / / /
13 / / / /
14 / / / X
15 / / / /
16 X X X X
17 / X / /
18 / / / /
19 / / / /
20 / / X /

TKO

Counting rows with criteria
 

Try this

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim num As Long

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

num = 0

With ActiveSheet
For Lrow = Lastrow To Firstrow Step -1

If Application.WorksheetFunction.CountIf(.Rows(Lrow), "x") 0 Then num = num + 1

Next
End With

MsgBox num

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message ups.com...
Hello,

I've been looking for a solution to this problem, I need to get a count
of all rows that have an "X", but not the total number of "X" in the
range. Any solutions or tips? Thanks!

A B C D
1 / / / /
2 X X X X
3 / / / /
4 / / X /
5 X X X X
6 / X / /
7 X X X X
8 X X X X
9 / / / X
10 / / X X
11 / / / /
12 / / / /
13 / / / /
14 / / / X
15 / / / /
16 X X X X
17 / X / /
18 / / / /
19 / / / /
20 / / X /

TKO

Counting rows with criteria
 

The tips have been very helpful!

For the formula that Bob provided,
=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A25)-1,,1,2),"*X*")0)), which
variables need to be updated if I move my data, say from A1:E25 to
C12:G37 (I need to insert some columns and rows surronding the table of
data)?

Thank you!
TKO

Counting rows with criteria
 

=SUMPRODUCT(--(COUNTIF(OFFSET(C12,ROW(A1:A25)-1,,1,2),"*X*")0))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
oups.com...
The tips have been very helpful!

For the formula that Bob provided,
=SUMPRODUCT(--(COUNTIF(OFFSET(A1,ROW(A1:A25)-1,,1,2),"*X*")0)), which
variables need to be updated if I move my data, say from A1:E25 to
C12:G37 (I need to insert some columns and rows surronding the table of
data)?

Thank you!
TKO