Comparing Absolute Column on multiple rows

[mortals]

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

 

[Tom Ogilvy]

=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

 

[mortals]

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

That would make a big difference.

 

[Tom Ogilvy]

=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.

 

[mortals]

It works!!! Its alive!!!

Now i feel a little stupid. But thanks Tom