Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT IF Help with multiple conditions | Excel Worksheet Functions | |||
count on multiple conditions | Excel Worksheet Functions | |||
COUNT using multiple conditions | Excel Discussion (Misc queries) | |||
Count with multiple conditions | Excel Worksheet Functions | |||
Count Based upon Multiple Conditions | Excel Worksheet Functions |