Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!

Try

=COUNTIF(A:A,"Red")

etc.

--
__________________________________
HTH

Bob

"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!

Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!

YES! With one additional paranthesis at the end, this works. Thank you- you
rock!!!

"Ragdyer" wrote:

Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!

Hi again- one more layer of complexity- let me know if you can solve this one.

So I want it to do all of the counting as outlined below but only if Column
B next to it does NOT say "dropped". Is this possible?
Example:
Col A Col B
Red pass
Red hold
Red dropped

In my example above, I would only want it to perform the formula calc below
if Col B does not say 'dropped'.

Thanks, Roady

"Ragdyer" wrote:

Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!

Try this:

=SUMPRODUCT((A1:A100={"Red","Orange","Yellow"})*(B 1:B100<"Dropped"))

Adjust the ranges to suit, but you can't use full-column references in
versions before XL2007.

Hope this helps.

Pete

On Jan 19, 4:25*pm, Roady wrote:
Hi again- one more layer of complexity- let me know if you can solve this one.

So I want it to do all of the counting as outlined below but only if Column
B next to it does NOT say "dropped". Is this possible?
Example:
Col A * *Col B
Red * * *pass
Red * * *hold
Red * * *dropped

In my example above, I would only want it to perform the formula calc below
if Col B does not say 'dropped'.

Thanks, Roady



"Ragdyer" wrote:
Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!- Hide quoted text -

- Show quoted text -

Hi Pete:

Thanks for your response - it works great for two conditions.

However, now I have a similar but different calculation I need to perform. I
tried to modify your formula to match three conditions but it doesn't seem to
be reading the 2nd condition I entered. I will paste below:

=SUMPRODUCT((D2:D16={"Red"})*(E2:E6=0)*(N2:N16="Dr opped"))

In the above example, I am trying to get a count/sum on how many lines meet
all 3 conditions: Col D=Red, Col E is blank/empty, and Column N is "Dropped".
You should not that Col E is a date field formatted thus: 01/01/09- could
that have something to do with it? Also- it reads many more lines longer than
these, I just shortened for your ease of reading.
Thanks again!


"Pete_UK" wrote:

Try this:

=SUMPRODUCT((A1:A100={"Red","Orange","Yellow"})*(B 1:B100<"Dropped"))

Adjust the ranges to suit, but you can't use full-column references in
versions before XL2007.

Hope this helps.

Pete

On Jan 19, 4:25 pm, Roady wrote:
Hi again- one more layer of complexity- let me know if you can solve this one.

So I want it to do all of the counting as outlined below but only if Column
B next to it does NOT say "dropped". Is this possible?
Example:
Col A Col B
Red pass
Red hold
Red dropped

In my example above, I would only want it to perform the formula calc below
if Col B does not say 'dropped'.

Thanks, Roady



"Ragdyer" wrote:
Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error. What I
want to do is count 1 each time any of the following words show up in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I only want
it to count 1 each time one of those colors shows up in the column and
give
me a total # in one cell. So, in the end all I need to know is the # of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!- Hide quoted text -

- Show quoted text -

You must keep the ranges the same size, and no need to input a single value
as an array

=SUMPRODUCT(--(D2:D16="Red"),--(E2:E16=0),--(N2:N16="Dropped"))


--
__________________________________
HTH

Bob

"Roady" wrote in message
...
Hi Pete:

Thanks for your response - it works great for two conditions.

However, now I have a similar but different calculation I need to perform.
I
tried to modify your formula to match three conditions but it doesn't seem
to
be reading the 2nd condition I entered. I will paste below:

=SUMPRODUCT((D2:D16={"Red"})*(E2:E6=0)*(N2:N16="Dr opped"))

In the above example, I am trying to get a count/sum on how many lines
meet
all 3 conditions: Col D=Red, Col E is blank/empty, and Column N is
"Dropped".
You should not that Col E is a date field formatted thus: 01/01/09- could
that have something to do with it? Also- it reads many more lines longer
than
these, I just shortened for your ease of reading.
Thanks again!


"Pete_UK" wrote:

Try this:

=SUMPRODUCT((A1:A100={"Red","Orange","Yellow"})*(B 1:B100<"Dropped"))

Adjust the ranges to suit, but you can't use full-column references in
versions before XL2007.

Hope this helps.

Pete

On Jan 19, 4:25 pm, Roady wrote:
Hi again- one more layer of complexity- let me know if you can solve
this one.

So I want it to do all of the counting as outlined below but only if
Column
B next to it does NOT say "dropped". Is this possible?
Example:
Col A Col B
Red pass
Red hold
Red dropped

In my example above, I would only want it to perform the formula calc
below
if Col B does not say 'dropped'.

Thanks, Roady



"Ragdyer" wrote:
Cumulative formula:
=Sum(countif(A:A,{"Red","Yellow","Orange"})

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"Roady" wrote in message
...
Hi:

I am having trouble devising a formula that doesn't have an error.
What I
want to do is count 1 each time any of the following words show up
in a
column and have it totaled.

For example:
Red
Orange
Yellow

The column might have more than those three colors entered but I
only want
it to count 1 each time one of those colors shows up in the column
and
give
me a total # in one cell. So, in the end all I need to know is the
# of
instances that the column has either Red, Orange, or Yellow in it.

Thanks!- Hide quoted text -

- Show quoted text -