Help with Count Please

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan Bernardo - 18 Feb 2006 17:46 GMT

I need the correct formula to count the following

A B

1 5 FW
2 3 G
3 4 G
4 3 R

I'd like to count the "B" Column but not when "3" appears in the "A" column.
The result of the above would be "2", since column "A" has a "3" in rows "2"
and "4".

Thanks in advance,

Alan

Reply to this Message

Bob Phillips - 18 Feb 2006 17:56 GMT

You haven't answered Dave's question.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I need the correct formula to count the following
>
[quoted text clipped - 12 lines]
>
> Alan

Reply to this Message

Alan Bernardo - 18 Feb 2006 18:36 GMT

| You haven't answered Dave's question.
|
[quoted text clipped - 16 lines]
| >
| > Alan

Aren't you just counting the number of non-3's in column A?

=COUNTIF(A1:A4,"<>"&3)

Well, I want to do that, yes, but I'm not counting column A per se. I'm
counting column B. If column A has a "3" then I don't count what is in
column B.

The "FW" percentage is calculated elsewhere and I don't want it included in
that calculation if a "3" is in column A. Does that clarify things? :)

Thanks.

Alan

Reply to this Message

Bob Phillips - 18 Feb 2006 19:04 GMT

Well Dave's answer seems to do just that to me.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> | You haven't answered Dave's question.
> |
[quoted text clipped - 31 lines]
>
> Alan

Reply to this Message

Dave Peterson - 18 Feb 2006 19:09 GMT

Are you trying to sum the stuff in column B only if column A is not a 3? I
guess I don't understand Count and what FW means...

=sumproduct(--(a1:a4<>3),b1:b4)

=sumproduct() likes to work with numbers. The -- stuff converts true/falses to
1's and 0's.

I bet that this is not the answer. You may want to explain what you really want
one more time.

> | You haven't answered Dave's question.
> |
[quoted text clipped - 38 lines]
>
> Alan

Signature

Dave Peterson

Reply to this Message

Alan Bernardo - 18 Feb 2006 19:28 GMT

| Are you trying to sum the stuff in column B only if column A is not a 3? I

Yes, I want to count the instances of "FW" in column B; but not if column A
has a "3" in it.

What I'm actually working on is a golf spreadsheet that calculates certain
statistics. In another part of my calculation, I'm percentaging the times a
ball is hit in the safely, which happens when it is hit either in the
fairway ("FW") or on the green "G"). But when counting greens hit, which
I'm concerned with here, the greens hit does not include par 3s. So while I
want to include the "FW" in one calculation, I don't want to include it
under another calculation (i.e., when the hole is a par 3).

I hope this clarifies things. :)

Thanks,

Alan

Reply to this Message

Dave Peterson - 18 Feb 2006 19:56 GMT

=sumproduct(--(a1:a4<>3),--(b1:b4="FW"))

> | Are you trying to sum the stuff in column B only if column A is not a 3?
> I
[quoted text clipped - 15 lines]
>
> Alan

Signature

Dave Peterson

Reply to this Message

Alan Bernardo - 18 Feb 2006 20:05 GMT

| =sumproduct(--(a1:a4<>3),--(b1:b4="FW"))

That did it. This formula might have been already mentioned, but that I
somehow didn't get it right.

Thanks.

Alan

Reply to this Message

via135 - 18 Feb 2006 19:34 GMT

hi Alan!

who don't you try

=SUMPRODUCT(--(A1:A4<>3),--(ISTEXT(B1:B4)))

-via135

Dave Peterson Wrote:

> Are you trying to sum the stuff in column B only if column A is not a 3?
> I
[quoted text clipped - 62 lines]
>
> Dave Peterson

Signature

via135

Reply to this Message