Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,420
Default 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!



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,572
Default 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!


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default 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!



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default 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!





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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 -


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default 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 -



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,420
Default 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 -





  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 69
Default 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!

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,420
Default 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!



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNT IF Help with multiple conditions potentus Excel Worksheet Functions 4 April 30th 08 05:02 PM
count on multiple conditions Debbie Excel Worksheet Functions 6 September 21st 06 06:34 PM
COUNT using multiple conditions SamGB Excel Discussion (Misc queries) 2 February 9th 06 11:12 PM
Count with multiple conditions Toby0924 Excel Worksheet Functions 3 February 2nd 05 02:35 PM
Count Based upon Multiple Conditions hkslater Excel Worksheet Functions 4 November 19th 04 05:43 AM


All times are GMT +1. The time now is 11:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"