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.

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.

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.

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.

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.

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.

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.

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..........

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.

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.

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.

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.

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.

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.