Help with Count Please

[Alan Bernardo]

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

 

[Bob Phillips]

You haven't answered Dave's question.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Alan Bernardo]

| You haven't answered Dave's question.
|
| --
| HTH
|
| Bob Phillips
|
| (remove nothere from email address if mailing direct)
|
| | > 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
| >
| >
|
|
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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

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.

 

[Alan Bernardo]

| 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

 

[via135]

hi Alan!

who don't you try

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

-via135

 

[Dave Peterson]

=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

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

 

[Alan Bernardo]

| =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