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

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)

"txlonghorn1989" wrote in message
oups.com...
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

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

"txlonghorn1989" wrote:

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

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

--
HTH

Bob Phillips

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

"Ryan Poth" wrote in message
...
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

"txlonghorn1989" wrote:

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

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" wrote:

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

--
HTH

Bob Phillips

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

"Ryan Poth" wrote in message
...
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

"txlonghorn1989" wrote:

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

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)

"Ryan Poth" wrote in message
...
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" wrote:

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

--
HTH

Bob Phillips

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

"Ryan Poth" wrote in message
...
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

"txlonghorn1989" wrote:

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