Using COUNTIF with Two or more Columns as Criteria

[JB]

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.

 

[RagDyer]

Try this:

=Sumproduct(('Sorted Data'!$A$2:$A$564="Apple")*('Sorted
Data'!E$2:E$564="Tree"))

 

[Mike H]

Try

=SUMPRODUCT((A1:A30="Apple")*(E1:E30="Tree"))

Change the range to suit

Mike

 

[Gaurav]

Try this

=SUMPRODUCT(--('Sorted Data'!$A$2:$A$564="Apple")*('Sorted
Data'!$E$2:$E$564="Tree"))

 

[JB]

Sorry guys, but none of these 3 suggestions would work. Still get the
formula error message.

 

[Gaurav]

What error message are you getting?

Sorry guys, but none of these 3 suggestions would work. Still get the
formula error message.

 

[JB]

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

 

[Pete_UK]

Don't type the formula, as you are likely to make mistakes - just copy
it from your newsreader and paste it into Excel.

Pete

 

[RagDyer]

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

 

[JB]

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!

 

[Gaurav]

Don't hate Excel. Would you like to send your file?

 

[RagDyer]

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.

 

[JB]

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.