Hello all, great board;
I have a list of items a user can select from a list. A short version is;
Beef
Sheep
Corn
Wheat
Beats
Ovine
In one column we store the results of what they checked so we can have any combination of from the list. eg, [ Beef, Corn, Wheat ] in another row I'll have [ Corn, Sheep, Ovine ].
I would like to be able to count the rows that have Corn or Wheat or Beats in them and have that row count as 1. I would also like to count the rows that have Beef or Sheep or Ovine in the them and have them count as 1.
There's 30,000 rows and I would like to know how many have crops, and how many have livestock.
I've tried =COUNTIF(V2:V30000,"*Wheat*")+COUNTIF(V2:V30000,"*Corn*")
but I think I'm getting the row counted twice.
Do you need the wildcards in the criteria? Try
=COUNTIF(V2:V30000,"Wheat")+COUNTIF(V2:V30000,"Corn")
Hi Nickalbu,
Welcome to the forum.
In your example, you want to count the rows as 1 which can have Beef, Corn, Wheat. So if I say that basis your data the count for the said category is 1 will that be fine and similarly 1 for other category set?
I suggest you to put a sample scenario in a sheet and attached for better understanding of forum members. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey
+919810929744
dilipandey@gmail.com
Look formula in G1 (formulas in F column are for checking and can be removed)...
I use numbers but same will be for words...
"Relax. What is mind? No matter. What is matter? Never mind!"
Hi Nickalbu, try this,
=SUMPRODUCT(SIGN(ISNUMBER(SEARCH("corn",V2:V30000))+ISNUMBER(SEARCH("wheat",V2:V30000))))
This will count 'wheat' or 'corn' contains, BUT if both 'wheat' or 'corn' contains in a cell will count as 1 NOT 2
HTH; Haseeb
If your problem is solved, please say so clearly, and mark your thread as Solved:
Forum Rules & How to Mark a thread as SOLVED
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks