COUNTIF

  • Thread starter Thread starter Rudy
  • Start date Start date
R

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

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

rgds,

rudy
 
Check your cells with the data.

Make sure you don't have any extra spaces (leading or trailing) in cells in both
ranges.
 
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.
 
Maybe you have calculation set to manual???

You sure your data is in L1:M100?

I'm pretty much out of ideas.
 
Hi All,

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

rgds,

rudy
 
Back
Top