Hi guys,

I'm trying to run a check between two worksheets. One worksheet
contains a column of serial numbers. On the second worksheet, i have a
column of companies (100 different companies) and multiple rows (For
the sake of this example, 30 rows) of serial numbers assigned to them.

I've tried nesting a IF loop within a sum

=(SUM(IF(Sheet2!$A$1:$A$20=Sheet1!B2:B30,1,0)))

But apparently, that doesn't work. Just any way to flag the rows that
contain the serial numbers that I am looking up would help. Thanks.


-Simon

=SUM(IF(Sheet2!$A$1:$A$20=TRANSPOSE(Sheet1!$B$1:$B $30),1,0))

enter/commit with Ctrl+Shift+Enter rather than just enter since this is an
array formula

--
Regards,
Tom Ogilvy


"mortals" wrote in message
oups.com...
Hi guys,

I'm trying to run a check between two worksheets. One worksheet
contains a column of serial numbers. On the second worksheet, i have a
column of companies (100 different companies) and multiple rows (For
the sake of this example, 30 rows) of serial numbers assigned to them.

I've tried nesting a IF loop within a sum

=(SUM(IF(Sheet2!$A$1:$A$20=Sheet1!B2:B30,1,0)))

But apparently, that doesn't work. Just any way to flag the rows that
contain the serial numbers that I am looking up would help. Thanks.


-Simon

My mistake, I meant that the rows would be Sheet1!B1:AE1

That would make a big difference.

=SUM(IF(Sheet2!$A$1:$A$20=Sheet1!$B$1:$AE$1,1,0))

Should work when entered/committed with Ctrl+Shift+Enter rather than just
enter.

--
Regards,
Tom Ogilvy


"mortals" wrote in message
ups.com...
My mistake, I meant that the rows would be Sheet1!B1:AE1

That would make a big difference.

It works!!! Its alive!!!

Now i feel a little stupid. But thanks Tom