I've tried several different variations of COUNT, COUNTIF, COUNTIFS, SUMPRODUCT, but none seem to do what I want, except for SUMPRODUCT. The problem with SUMPRODUCT is that I get this "...out of resources..." error.
What I'm trying to do is count the number of time certain texts show up in a column. My range is B27:B171, and my criteria are "apples", "oranges", and "pears".
Thanks in advance for your help! This forum has been wonderful in helping me in the past!
Hi jrlafrance,
See it the attached helps.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Have you tried
=COUNTIF(B27:B171,"apples")+COUNTIF(B27:B171,"oranges")+COUNTIF(B27:B171,"pears")
Don't forget to click the little star to the left of this post if you feel I helped!
Taming the Excel dragon... www.TheExcelphile.com
Not sure i understand the problem..
=COUNTIF($B$27:$B$127,"apples") should return the count of "apples", or use a cell reference containing what you are looking for...
is the criteria mixed in with other text in the cells?
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
This is the right track. When I copy your formula above, it works. But, I'm trying to count 16 different kinds of fruit when there are 21 possible different fruits in the column. Is there a maximum numbers of criteria it can count? I wouldn't think so...Originally Posted by TheExcelphile
Yes, I do have many other fruits mixed in the column, but only 1 fruit in each cell.
Here is the formula that I have in the cell:
=COUNTIF(B27:B171,F11)+COUNTIF(B27:B171,F10)+COUNTIF(B27:B171,F9)+COUNTIF(B27:B171,F8)+COUNTIF(B27:B171,F7)+COUNTIF(B27:B171,F6)+COUNTIF(B27:B171,F5)+COUNTIF(B27:B171,D9)+COUNTIF(B27:B171,D8)+COUNTIF(B27:B171,D7)+COUNTIF(B27:B171,D6)+COUNTIF(B27:B171,D5)+COUNTIF(B27:B171,D13)+COUNTIF(B27:B171,D12)+COUNTIF(B27:B171,D11)+COUNTIF(B27:B171,D10)
but the result isn't really a result. What is displayed in the cell is the formula itself. Not sure how that happened!
Could you upload the sheet where you are facing the problem?
then they go away..
try also sumproduct
=SUMPRODUCT((B27:B171={"apple","banana","orange"})+0)
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
are you trying to count only the cells that have "apples" and "oranges" and "pears"? if so TheXcelphile's first post should work perfectly..
Or
Formula:
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
I apprecitate everyone's help! Sixthsense formula is the one that I needed!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks