Comparing 2 or more columns and returning a true value

Hi Guys,

I'm quite new to array formulas in excel and was wondering whether or not I can advance on this formula:

=AND($C$5=B7,$K$5=G5)

The problem is I would like to compare the entire colums (or array) and return the number of 'TRUE' statements in the array as a value.

=AND($C$5:$C$65536=B7,$K$5:$K$65536=G5)

however, this returns all values as 'FALSE'

example of arrgument:

IF C5:C65535=customer then count how many cells in K5:K65536=completed

Any advice greatly welcomed.

Cheers

Stu

Keep you range sizes to a minimum when dealing with arrays... build in some excess capacity but don't use include vast numbers of blank rows.

Please Login or Register  to view this content.

Pre 2007 you can't use entire column references - ie C:C

EDIT: I'm also going to get into trouble for replying as this is plainly in the wrong forum (I've just discovered) ... please PM a Mod and ask that they move it for you to the appropriate forum (namely Worksheet Functions)

I don't understand your question at all I'm afraid.

-- is the double unary operator and is used to coerce the Boolean results of the tests
(ie x = y will return TRUE/FALSE ... --(TRUE/FASLE) => 1/0 respectively)

If you're getting a 0 result it's because the criteria values (B7, G5) do not match the values in the ranges specified (C and K respectively).

We could begin a long thread now trying to establish the cause be it trailing spaces, differing data types etc but the simplest thing would be to post a small sample file so I can see with my own eyes.

The formula you entered

Please Login or Register  to view this content.

bears virtually no relation to that offered.

It should be:

Please Login or Register  to view this content.