| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLS HLP! Countifs Syntax Problem?
Hi,
Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLS HLP! Countifs Syntax Problem?
where "apple" is in A6 and your data is in A1:B3, try:
=COUNTIF(A1:B3,"*"&A6) "dee" wrote: Hi, Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLS HLP! Countifs Syntax Problem?
Hi and thanks for your response.
The problem is when I'm trying to count two separate items in two ranges using the CountifS function is Excel 2007. Would you know about that? Thank you. -- Thanks! Dee "JMB" wrote: where "apple" is in A6 and your data is in A1:B3, try: =COUNTIF(A1:B3,"*"&A6) "dee" wrote: Hi, Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLS HLP! Countifs Syntax Problem?
I don't have XL07. From your post, I did not get that you wanted to do a
multiple criteria count (which is what I understand countifs is intended). I thought your request was to count the cells w/apples or grapes, which I think would be Countif(...., "*apple")+Countif(....., "*grape") which returns 5 for me. If this isn't what you want, you should include additional details about what you want to do, the formula you are currently using, and what answer you are expecting for the sample data given and hopefully someone w/XL07 will offer a suggestion. "dee" wrote: Hi and thanks for your response. The problem is when I'm trying to count two separate items in two ranges using the CountifS function is Excel 2007. Would you know about that? Thank you. -- Thanks! Dee "JMB" wrote: where "apple" is in A6 and your data is in A1:B3, try: =COUNTIF(A1:B3,"*"&A6) "dee" wrote: Hi, Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLS HLP! Countifs Syntax Problem?
=SUM(COUNTIF(A1:B4,{"*apple*","*grapes*"}))
"dee" wrote: Hi, Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLS HLP! Countifs Syntax Problem?
Hi Dee
Counifs does not work in the way you are thinking. =COUNTIFS(A1:A4,"*apples",B1:B4,"*grapes") will only return a result when apples and grapes occur in the same row In your example it will be 0 If Green Grapes and Oranges were transposed in column B, then you would get a result of 1, as both Apples and Grapes appear on row 1 If you used the following formula on this amended data however, it will still return 0, as the correct criteria are not being set =COUNTIFS(A1:B4,"*apples",A1:B4,"*Grapes") You have been given the correct method in either JMB's second post or Teethless Mama's post. There is no different solution in XL2007 to previous versions. -- Regards Roger Govier "dee" wrote in message ... Hi and thanks for your response. The problem is when I'm trying to count two separate items in two ranges using the CountifS function is Excel 2007. Would you know about that? Thank you. -- Thanks! Dee "JMB" wrote: where "apple" is in A6 and your data is in A1:B3, try: =COUNTIF(A1:B3,"*"&A6) "dee" wrote: Hi, Let's say I have the following in two columns, with a cell that contain the criteria I wish to count: red apple orange pear green applies peach cherries red grapes green grapes apple grapes I wish to use Countifs to count the number of apples, no matter the colour, in both ranges, and grapes, no matter the colour, in both ranges. I have tried and tried, but seem to always come up with 0. PLS HLP! -- Thanks! Dee |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Incorrect syntax near '#' problem when returning to MS Query fromExcel | Excel Discussion (Misc queries) | |||
| Delete syntax problem | Excel Worksheet Functions | |||
| SendKeys syntax problem | New Users to Excel | |||
| .XValues syntax problem | Charts and Charting in Excel | |||
| Syntax problem | Excel Worksheet Functions | |||
Linear Mode
