Hi Rob
I'm still not really following you. I thought you were trying to use
Conditional Formatting to colour cells if certain criteria were met but
I don't think that is what you were asking.
Now, I think you are trying to count the total number of cases where the
name on Sheet1!A1 exists in Sheet2!A1:A1500 and Sheet1!B1="HERE"
Consider the following Data
Sheet1
A B
1 Roger Here
2 Jack Here
3 Mandy Here
4 Joe Here
Sheet2
A
1 Roger
2 Jim
3 Mandy
4 Jack
If you want a count of 1 if the name in column A of Sheet1 exists in
column A of Sheet2 and alongside the name the word Here exists in column
B of Sheet1 then the result would be a 1 in rows 1, 2 and 3 for a total
of 3
If you wanted there to be an exact match between the names in Sheet1
column A and Sheet2 column A whilst column B holds Here, then there
would only be a count of 1 in rows 1 and 3 for a total of 2.
If you are using a separate column (AA) then for the first case above,
use the formula in AA1 of
=--AND(COUNTIF(Worksheet2!$A$1:$A$1500,$A1),$B1="Here ") and copied down
If it is the second case, then use a formula in AA1 of
=--AND($A1=Worksheet2!$A1, $B1="HERE") and copied down
Your total number will just be =SUM(A:A) for either scenario. No Array
formulae involved.
I don't understand why you say Sumproduct caused you all the problems
with size and speed.
For case 2 above the single formula of
=SUMPRODUCT(--($A$1:$A$1500=Worksheet2!$A$1:$A$1500),--($B$1:$B$1500="HERE"))
will give you the total number of matches.
For case 1 then the single array formula
{=SUM(COUNTIF($A$1:$A$1500,Worksheet2!$A$1:$A$1500 )*--($B$1:$B$1500="Here"))}
will give you the result of 3 without using column AA for the other
formulae.
The Sumproduct or Array formulae do not need to be copied across 15
columns, or down 80 rows.
But maybe I am still not understanding exactly what you are trying to
do..
If these points do not solve your problems than you may, if you wish,
send me a copy of your workbook with the real life examples and a note
of what you are trying to do and I will take a look for you. Remove the
NOSAPM from my email address to send direct.
--
Regards
Roger Govier
"systemx" wrote
in message ...
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