Re: COUNTIF

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi All,

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

rgds,

rudy

"Fred Smith" wrote:

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.

"Rudy" <Rudy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D3D774FC-AD45-4276-8044-33D845A87378@xxxxxxxxxxxxxxxx
Hi,

Confirm there's no extra spaces in both coulums.

rgds,

rudy


"Dave Peterson" wrote:

Check your cells with the data.

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



Rudy wrote:

Hi,

I've just copy & paste Biff's formula below on my spread***, 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" <Rudy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A036FBE9-7EDB-4FC0-A30F-799BF2531153@xxxxxxxxxxxxxxxx
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" <Rudy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A57D6DC6-E93F-450F-B011-AD6DBAB5D045@xxxxxxxxxxxxxxxx
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






--

Dave Peterson



.

Quantcast