| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using COUNTIF with Two or more Columns as Criteria
I am trying to use COUNTIF to look at two or more columns. For example,
"Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Try this:
=Sumproduct(('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!E$2:E$564="Tree")) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Try
=SUMPRODUCT((A1:A30="Apple")*(E1:E30="Tree")) Change the range to suit Mike "JB" wrote: I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Try this
=SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Sorry guys, but none of these 3 suggestions would work. Still get the
formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
What error message are you getting?
"JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Well, when I enter it exactly the way you typed it, with the hyphens, equal
signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Don't type the formula, as you are likely to make mistakes - just copy
it from your newsreader and paste it into Excel. Pete On May 6, 10:17*pm, JB wrote: Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. *When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
What's chances that there's *more* in the cell then just the words (apple,
tree) that you're looking for? Could there be invisible characters or spaces, which might be present if this data was imported from other apps or the web? See if this works: =SUMPRODUCT((ISNUMBER(SEARCH("apple",'Sorted Data'!$A$2:$A$564)))* (ISNUMBER(SEARCH("tree",'Sorted Data'!E$2:E$564)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Are you sure that the relevant cells contain only "Apple" and "Tree", and
that there aren't extra characters (perhaps spaces or non-printing characters)? =LEN(A2) and =LEN(E2) will give you the lengths of the relevant strings, and these can be copied down to the relevant rows to check whether they return 5 and 4 respectively for "Apple" and "Tree". -- David Biddulph "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
There is nothing more in the cells. I checked.
When I copy and paste your suggestions below in, I at least get an answer, but it's the wrong answer. I get 90, which is the total number of rows that have "Apple" in it. There are 200 rows with "Tree" in it and 25 rows with both "Apple" and "Tree", so I should be getting 25 for the answer. This kind of crap makes one hate Excel! "RagDyer" wrote: What's chances that there's *more* in the cell then just the words (apple, tree) that you're looking for? Could there be invisible characters or spaces, which might be present if this data was imported from other apps or the web? See if this works: =SUMPRODUCT((ISNUMBER(SEARCH("apple",'Sorted Data'!$A$2:$A$564)))* (ISNUMBER(SEARCH("tree",'Sorted Data'!E$2:E$564)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Don't hate Excel. Would you like to send your file?
"JB" wrote in message ... There is nothing more in the cells. I checked. When I copy and paste your suggestions below in, I at least get an answer, but it's the wrong answer. I get 90, which is the total number of rows that have "Apple" in it. There are 200 rows with "Tree" in it and 25 rows with both "Apple" and "Tree", so I should be getting 25 for the answer. This kind of crap makes one hate Excel! "RagDyer" wrote: What's chances that there's *more* in the cell then just the words (apple, tree) that you're looking for? Could there be invisible characters or spaces, which might be present if this data was imported from other apps or the web? See if this works: =SUMPRODUCT((ISNUMBER(SEARCH("apple",'Sorted Data'!$A$2:$A$564)))* (ISNUMBER(SEARCH("tree",'Sorted Data'!E$2:E$564)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
Click on the cell where you entered the formula.
Select the *entire* formula *IN THE FORMULA BAR*. Right click in that selection and choose "Copy". Hit <Enter Reply to this post and paste that formula into the reply, and post it. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... There is nothing more in the cells. I checked. When I copy and paste your suggestions below in, I at least get an answer, but it's the wrong answer. I get 90, which is the total number of rows that have "Apple" in it. There are 200 rows with "Tree" in it and 25 rows with both "Apple" and "Tree", so I should be getting 25 for the answer. This kind of crap makes one hate Excel! "RagDyer" wrote: What's chances that there's *more* in the cell then just the words (apple, tree) that you're looking for? Could there be invisible characters or spaces, which might be present if this data was imported from other apps or the web? See if this works: =SUMPRODUCT((ISNUMBER(SEARCH("apple",'Sorted Data'!$A$2:$A$564)))* (ISNUMBER(SEARCH("tree",'Sorted Data'!E$2:E$564)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using COUNTIF with Two or more Columns as Criteria
I would send the file, but it contains proprietary company info so I can't.
I appreciate all of the help you folks provide though. "Gaurav" wrote: Don't hate Excel. Would you like to send your file? "JB" wrote in message ... There is nothing more in the cells. I checked. When I copy and paste your suggestions below in, I at least get an answer, but it's the wrong answer. I get 90, which is the total number of rows that have "Apple" in it. There are 200 rows with "Tree" in it and 25 rows with both "Apple" and "Tree", so I should be getting 25 for the answer. This kind of crap makes one hate Excel! "RagDyer" wrote: What's chances that there's *more* in the cell then just the words (apple, tree) that you're looking for? Could there be invisible characters or spaces, which might be present if this data was imported from other apps or the web? See if this works: =SUMPRODUCT((ISNUMBER(SEARCH("apple",'Sorted Data'!$A$2:$A$564)))* (ISNUMBER(SEARCH("tree",'Sorted Data'!E$2:E$564)))) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JB" wrote in message ... Well, when I enter it exactly the way you typed it, with the hyphens, equal signs, and quotation marks in the exact place you have them I get zero for the answer, but I manually counted and get 25. When I eliminate the hyphens and/or put the equal signs within the quotation marks ("=Apple"), I get the standard formula error message. I'm wondering if the fact that I'm using Excel 2003 matters.......... "Gaurav" wrote: What error message are you getting? "JB" wrote in message ... Sorry guys, but none of these 3 suggestions would work. Still get the formula error message. "Gaurav" wrote: Try this =SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted Data'!$E$2:$E$564="Tree")) "JB" wrote in message ... I am trying to use COUNTIF to look at two or more columns. For example, "Apple" is listed several times in Column A and Tree is listed several times in Column E. I want to count all of the occasions where "Apple" in Column A and "Tree" in Column E are present together. I wrote the function like this, but couldn't get it to return the right answer: =COUNTIF('Sorted Data'!$A$2:$A$564,"=Apple") + COUNTIF('Sorted Data'!E$2:E$564,"=Tree") It seems as though the "+" sign is telling it to count both the times "Apple" is in Column A and when "Tree" is in Column E, so I get the total number of times "Apple" is in Column A, plus the times "Tree" is in Column E. How can I get it to only count the number of times that "Apple" is in Column A and "Tree" is in Column E at the same time? This seems like a simple fix, but I cannot figure out what to put in there in place of the "+" sign. I tried "or", but no luck. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| countif two columns two criteria | Excel Worksheet Functions | |||
| Countif Criteria (2 diff columns) | Excel Discussion (Misc queries) | |||
| COUNTIF with criteria in 2 columns | Excel Discussion (Misc queries) | |||
| Countif using criteria in multiple columns | Excel Worksheet Functions | |||
| Countif - Two Criteria in two columns are met. | Excel Discussion (Misc queries) | |||
Linear Mode
