COUNTIF

[Rudy]

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

 

[T. Valko]

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).

 

[Rudy]

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

 

[Fred Smith]

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]

Hi,

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

rgds,

rudy

 

[Dave Peterson]

Check your cells with the data.

Make sure you don't have any extra spaces (leading or trailing) in cells in both
ranges.

 

[Rudy]

Hi,

Confirm there's no extra spaces in both coulums.

rgds,

rudy

 

[Fred Smith]

Rudy, you have two choices. You can choose to be a victim, or you can find
the solution.

Right now, you are choosing the victim route. You want us to commiserate
with you that it's not your fault you can't find a solution.

But if you really want a solution, it won't be that hard to find. Check for:
* Are your ranges correct? Is your data really in L1:L100 and M1:M100?
* Is the underlying data correct?
* What happens if you break down the process into steps?

What happens if you check for just the Y and the N? What happens if you do a
Countif against the data?

The solution is there. You just need to be motivated to find it.

Regards,
Fred.

 

[Dave Peterson]

Maybe you have calculation set to manual???

You sure your data is in L1:M100?

I'm pretty much out of ideas.

 

[Rudy]

Hi All,

Biff's formula works, Fred was right, needs a bit of motivation. Thanks guys

rgds,

rudy