How to increase counter based on values in 2 different cells

[txlonghorn1989]

I want to check 2 cells (2 different columns) for a worksheet.

Easy to do programmatically but not familiar with spreadsheet
functions. Here's what I want to do in psuedo code...

For every row (with data) in a worksheet
If column X cell = 1 AND column Y cell >= 50
ctr = ctr + 1

Any help would be appreciated.

Mike

 

[Bob Phillips]

Dim ctr As Long
Dim oRow As Range

For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "X").Value = 1 And _
Cells(oRow.Row, "Y").Value >= 50 Then
ctr = ctr + 1
End If
Next oRow


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Not sure if this is *precisely* what you need, but you could try this formula:

=SUM((X1:X65535=1)*(Y1:Y65535>=50))

This must be array-entered (CTRL-SHIFT-ENTER)

Logically, I would think that this could be simplified to:

=SUM((X:X=1)*(Y:Y>=50))

but I get #NUM! as a result when I do that. I'm not sure why.

HTH,
Ryan

 

[Bob Phillips]

because array formulae just don't work with complete columns.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob. I didn't know that (probably since I'd never tried it). Do you
think that could be classified as a bug, or can you think of any intended
reason for that? Just wondering.

Ryan

 

[Bob Phillips]

No I don't think it is a bug, I think it was a design decision when building
the early versions of Excel which have carried forward.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)