Thank you both for your help.

I ended up running with the following formula -

{=SUM(IF('Worksheet1!'$A$1:$A$1500='Worksheet2!'$A 1,IF('Worksheet1!'$B$1:$B$1500="Here",'Worksheet1! "$AA$1:$AA$1500,0),0))}

I have tested it at home and it performs the function I need - although
until I try it at work I am not sure how using the array formula in such
a large worksheet (it will be copied through around 80 rows, across
around 15 columns) will affect performance and file size.

Hopefully what I was aiming for will now become a little clearer! I had
to use column AA to do some workings....

=IF(B1="Here",1,0)

This gives the 'SUM' element something to add up!

The sumproduct function - while very handy - caused the file size to
blow out incredibly and makes my worksheet come to a grinding halt - so
was not the ideal way to go!

Roger....you were spot on in making sense of what I wanted to say!
Although I am still unsure how the AND function will work in giving the
result I need...probably my own stupidity.

In the scenario I have 80 names appearing randomly....all either 'Here'
or 'Not Here' - basically a roll call. The only way I could think of to
get the AND function working was to make a matrix 80 columns wide for
each person, and fill down through each row. It would then only show
TRUE if the person was here for that day. I could then use a COUNT
function to determine on how many days that person was here.......while
it will definately work....it would be a little messy. But if the array
formula slows performance...it is definately an option.

Thank you again to both for the help! I really appreciate and my
apologies for the lengthy response!

Cheers

Rob :)


--
systemx
------------------------------------------------------------------------
systemx's Profile: http://www.excelforum.com/member.php...o&userid=29254
View this thread: http://www.excelforum.com/showthread...hreadid=525182