Counting a subset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kent McPherson - 04 Dec 2007 18:58 GMT

I have data with multiple columns. I want to count all items in column A =
"X" and with column B > 250. I know how to do either condition but I need
to join the two. Any help would be appreciated. Thanks!

Reply to this Message

JLGWhiz - 04 Dec 2007 19:31 GMT

This could do it.

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = "X" and Cells(i, 1).Offset(0, 1) > 250 Then
'Do things
End If
Next

> I have data with multiple columns. I want to count all items in column A =
> "X" and with column B > 250. I know how to do either condition but I need
> to join the two. Any help would be appreciated. Thanks!

Reply to this Message

excelent - 04 Dec 2007 19:35 GMT

=sumproduct((a1:a1000="X")*(b1:b1000>250))
change range to fit

"Kent McPherson" skrev:

Reply to this Message

Kent McPherson - 10 Dec 2007 14:16 GMT

Thanks, I've tried this formula but it doesn't give me the right answer.
For example, I should get an answer of 3 but it gives me 10. If I change
either parameter to 1, I get the proper count but when it's together, I get
the wrong answer. Is there any way to debug? Suggestions?

> =sumproduct((a1:a1000="X")*(b1:b1000>250))
> change range to fit
[quoted text clipped - 6 lines]
>> need
>> to join the two. Any help would be appreciated. Thanks!

Reply to this Message

JLGWhiz - 04 Dec 2007 19:36 GMT

Sorry Kent, I forgot to put the counter on it.

Sub cnt()
Counter = 0
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = "X" and Cells(i, 1).Offset(0, 1) > 250 Then
'Do things
Counter = Counter + 1
End If
Next
MsgBox Counter
End Sub

Reply to this Message