Hi all,

Probably a relatively simple question - I'm hoping someone can help.

Basically, I want to write a conditional formula without using an
array.

Something like -

COUNT IF 'worksheet1' A:A = 'worksheet2' A1 AND 'worksheet1' B:B =
"HERE".

I have been playing around with this for some time, and have had little
success. The multiple argument seems to throw me out...in addition there
are currently 1500 rows of data (and growing) so there will be many
occurences of the 'worksheet2' A1 value on worksheet 1.

So basically, the formula will go through each row of data, check to
see if two values match, if they do, check the third value is 'HERE' -
and count +1.

I realise I could use a sumproduct formula - but because of the amount
of data performance loss is terrible so this is not a viable option.

Any ideas?

Thank you

Rob


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

Hi Rob,

Check this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Kind regards,

Niek Otten

"systemx" wrote in message
...

Hi all,

Probably a relatively simple question - I'm hoping someone can help.

Basically, I want to write a conditional formula without using an
array.

Something like -

COUNT IF 'worksheet1' A:A = 'worksheet2' A1 AND 'worksheet1' B:B =
"HERE".

I have been playing around with this for some time, and have had little
success. The multiple argument seems to throw me out...in addition there
are currently 1500 rows of data (and growing) so there will be many
occurences of the 'worksheet2' A1 value on worksheet 1.

So basically, the formula will go through each row of data, check to
see if two values match, if they do, check the third value is 'HERE' -
and count +1.

I realise I could use a sumproduct formula - but because of the amount
of data performance loss is terrible so this is not a viable option.

Any ideas?

Thank you

Rob


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

Hi Rob

Your suggested formula is looking to see if the value from Worksheet2!A1
occurs anywhere within column A of Worksheet1 and the word "HERE"
appears anywhere in column B of Worksheet1.
From your description it sounds as though what you are really looking
for is
whether A1 of Sheet1 = A1 of Sheet2 and B1 of Sheet1 = "Here"
and so on down the column.

If I have understood you correctly therefore, your Conditional
Formatting formula should be
=AND(Worksheet1!$A1=Worksheet2!$A1, Worksheet1!$B1="HERE")
Because the row reference are relative, the check will refer to each row
in turn the CF is applies to.

--
Regards

Roger Govier


"systemx" wrote
in message ...

Hi all,

Probably a relatively simple question - I'm hoping someone can help.

Basically, I want to write a conditional formula without using an
array.

Something like -

COUNT IF 'worksheet1' A:A = 'worksheet2' A1 AND 'worksheet1' B:B =
"HERE".

I have been playing around with this for some time, and have had
little
success. The multiple argument seems to throw me out...in addition
there
are currently 1500 rows of data (and growing) so there will be many
occurences of the 'worksheet2' A1 value on worksheet 1.

So basically, the formula will go through each row of data, check to
see if two values match, if they do, check the third value is 'HERE' -
and count +1.

I realise I could use a sumproduct formula - but because of the amount
of data performance loss is terrible so this is not a viable option.

Any ideas?

Thank you

Rob


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

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

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