Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
Hello...
I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
=SUMPRODUCT(--(A2:A500=5),--(B2:B500="years"))
"Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
Assuming your data are in Col a and Col B
you need to add a helper column in Col C, place this formula in C2 and copy down =A2&" "&B2 oe you can place this in a faraway col and hide it in col D, place this formula =COUNTIF(C2:C11,"5 years") adjust this to your range -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
Thanks for the help!
Now if i add a third column to spreadsheet....like this: 5 years tall 5 year short 3 months tall/sick 5 years short/sick 5 years sick/tall and I enter the function: =sumproduct(--(a1:a500=5),--(b1:b500="y*"),--(c1:c500="*tall*")) I get a #NUM error... I need to search with "y*" because sometimes year maybe spelled incorectly, also I need to search the third column for "*tall*" as it may be with additional text seperated by a forward slash (/). Any additional suggestions? "N harkawat" wrote: =SUMPRODUCT(--(A2:A500=5),--(B2:B500="years")) "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
Thanks for the suggestion Francis, but I'd rather not concatenate the columns
into a new column. "Francis" wrote: Assuming your data are in Col a and Col B you need to add a helper column in Col C, place this formula in C2 and copy down =A2&" "&B2 oe you can place this in a faraway col and hide it in col D, place this formula =COUNTIF(C2:C11,"5 years") adjust this to your range -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
If you're using Excel 2007...
=COUNTIFS(A2:A11,5,B2:B11,"years") Better to use cells to hold the criteria: D2 = 5 E2 = years =COUNTIFS(A2:A11,D2,B2:B11,E2) -- Biff Microsoft Excel MVP "Ryan" wrote in message ... Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
One way:
=SUMPRODUCT((A1:A500=5)*(LEFT(B1:B500)="y")*(ISNUM BER(SEARCH("tall",C1:C500)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ryan" wrote in message ... Thanks for the help! Now if i add a third column to spreadsheet....like this: 5 years tall 5 year short 3 months tall/sick 5 years short/sick 5 years sick/tall and I enter the function: =sumproduct(--(a1:a500=5),--(b1:b500="y*"),--(c1:c500="*tall*")) I get a #NUM error... I need to search with "y*" because sometimes year maybe spelled incorectly, also I need to search the third column for "*tall*" as it may be with additional text seperated by a forward slash (/). Any additional suggestions? "N harkawat" wrote: =SUMPRODUCT(--(A2:A500=5),--(B2:B500="years")) "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
sometimes year maybe spelled incorectly
What? Are you kidding? Someone doesn't know how to spell a 4 letter word like "yeer"? <VBG Try this: =SUMPRODUCT(--(A1:A500=5),--(LEFT(B1:B500)="y"),--(ISNUMBER(SEARCH("tall",C1:C500)))) You can't directly use wildcards in SUMPRODUCT. -- Biff Microsoft Excel MVP "Ryan" wrote in message ... Thanks for the help! Now if i add a third column to spreadsheet....like this: 5 years tall 5 year short 3 months tall/sick 5 years short/sick 5 years sick/tall and I enter the function: =sumproduct(--(a1:a500=5),--(b1:b500="y*"),--(c1:c500="*tall*")) I get a #NUM error... I need to search with "y*" because sometimes year maybe spelled incorectly, also I need to search the third column for "*tall*" as it may be with additional text seperated by a forward slash (/). Any additional suggestions? "N harkawat" wrote: =SUMPRODUCT(--(A2:A500=5),--(B2:B500="years")) "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif function with multiple column criteria?
And what happens if I misspell year as: eyar?
Or spell month as: mynth. It may be better to look through the data and fix those spelling mistakes before trusting anything that comes out of it. "T. Valko" wrote: sometimes year maybe spelled incorectly What? Are you kidding? Someone doesn't know how to spell a 4 letter word like "yeer"? <VBG Try this: =SUMPRODUCT(--(A1:A500=5),--(LEFT(B1:B500)="y"),--(ISNUMBER(SEARCH("tall",C1:C500)))) You can't directly use wildcards in SUMPRODUCT. -- Biff Microsoft Excel MVP "Ryan" wrote in message ... Thanks for the help! Now if i add a third column to spreadsheet....like this: 5 years tall 5 year short 3 months tall/sick 5 years short/sick 5 years sick/tall and I enter the function: =sumproduct(--(a1:a500=5),--(b1:b500="y*"),--(c1:c500="*tall*")) I get a #NUM error... I need to search with "y*" because sometimes year maybe spelled incorectly, also I need to search the third column for "*tall*" as it may be with additional text seperated by a forward slash (/). Any additional suggestions? "N harkawat" wrote: =SUMPRODUCT(--(A2:A500=5),--(B2:B500="years")) "Ryan" wrote: Hello... I'm hoping someone, much smarter than I, can help me find a solution to my problem. Problem: My spreasheet looks like this (two columns): 1 day 2 months 5 years 6 weeks 5 years 7 days 1 month 3 years 1 day 6 weeks I want to use a count function to count the number of times 5 years occurs, and I am having a hard time getting a nestedif function to make this happen. Any suggesstions? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Countif with multiple criteria in the same column. | Excel Discussion (Misc queries) | |||
Countif Function w/ multiple Criteria | Excel Worksheet Functions | |||
Using the CountIf function with multiple criteria? | Excel Worksheet Functions | |||
Countif multiple criteria within the same column | Excel Discussion (Misc queries) | |||
COUNTIF or SUM function (Multiple criteria) HELP!! | Excel Worksheet Functions |