Hi,

I've just copy & paste Biff's formula below on my spreadsheet, however,
result still returns "0".

rgds,

rudy


"Fred Smith" wrote:

What is "it" that's returning zero? Show us the formula you are using so we
can help you. Biff's formula will work when used properly.

Regards,
Fred.

"Rudy" wrote in message
...
Hi,

It still returns zero. Don't know why. Below is the format. Just want to
count how many has NEW & Y, EXISTING & Y. Thanks.

L M

NEW Y
EXISTING N
NEW N
NEW Y
NEW Y
NEW N
EXISTING Y
EXISTING Y
NEW N
NEW Y




"T. Valko" wrote:

Try these:

=SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y"))

=SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y"))

Note that you can't use entire columns as range references with
SUMPRODUCT
(unless you're using Excel 2007).


--
Biff
Microsoft Excel MVP


"Rudy" wrote in message
...
Hi,

Guess I went to the wrong office discussion group (Access). Anyway, can
someone assist please. I have in column L the words NEW & EXISTING & in
column M the letters Y & N. What I need is to count how many cell that
has
NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've
tried
all sort of COUNTIF functions but it always return zero. Thanks.

rgds,

rudy