Conditional Formula - No array

[systemx]

Hi all,

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

Basically, I want to write a conditional formula without using a
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 littl
success. The multiple argument seems to throw me out...in addition ther
are currently 1500 rows of data (and growing) so there will be man
occurences of the 'worksheet2' A1 value on worksheet 1.

So basically, the formula will go through each row of data, check t
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 amoun
of data performance loss is terrible so this is not a viable option.

Any ideas?

Thank you

Rob :confused

 

[Niek Otten]

Hi Rob,

Check this:

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

 

[Roger Govier]

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.

 

[systemx]

Thank you both for your help.

I ended up running with the following formula -

{=SUM(IF('Worksheet1!'$A$1:$A$1500='Worksheet2!'$A1,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 - althoug
until I try it at work I am not sure how using the array formula in suc
a large worksheet (it will be copied through around 80 rows, acros
around 15 columns) will affect performance and file size.

Hopefully what I was aiming for will now become a little clearer! I ha
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 t
blow out incredibly and makes my worksheet come to a grinding halt - s
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 th
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 t
get the AND function working was to make a matrix 80 columns wide fo
each person, and fill down through each row. It would then only sho
TRUE if the person was here for that day. I could then use a COUN
function to determine on how many days that person was here.......whil
it will definately work....it would be a little messy. But if the arra
formula slows performance...it is definately an option.

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

Cheers

Rob :

 

[Roger Govier]

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.