Count and Sum Multiple Conditions in one column
 

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!

Count and Sum Multiple Conditions in one column
 

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!

Count and Sum Multiple Conditions in one column
 

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!

Count and Sum Multiple Conditions in one column
 

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!

Count and Sum Multiple Conditions in one column
 

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!

Count and Sum Multiple Conditions in one column
 

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 -

Count and Sum Multiple Conditions in one column
 

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 -

Count and Sum Multiple Conditions in one column
 

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 -

Subject: Count and Sum Multiple Conditions in one column
 

great- thanks!

So if in the last column, I do NOT want it to include 'dropped' or blank
cells- how do I indicate that? I tried doing the following but it is only
reading the 'dropped' and ignoring my request to eliminate the count if it is
blank cell in N.

=SUMPRODUCT(--(D2:D16="Red"),--(E2:E16=0),--(N2:N16<{"dropped",""}))

Am I using the wrong symbols to indicate a blank cell? Or is it impossible
to do more than one 'does not include' (<)contingency?
Thanks again!

Subject: Count and Sum Multiple Conditions in one column
 

That doesn't work I am afraid

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

--
__________________________________
HTH

Bob

"Roady" wrote in message
...
great- thanks!

So if in the last column, I do NOT want it to include 'dropped' or blank
cells- how do I indicate that? I tried doing the following but it is only
reading the 'dropped' and ignoring my request to eliminate the count if it
is
blank cell in N.

=SUMPRODUCT(--(D2:D16="Red"),--(E2:E16=0),--(N2:N16<{"dropped",""}))

Am I using the wrong symbols to indicate a blank cell? Or is it impossible
to do more than one 'does not include' (<)contingency?
Thanks again!