Numbers: searching for occurences of text (case sensitive)

Question

Level 2

368 points

Numbers: searching for occurences of text (case sensitive)

For biology class, I have a table for dihybrid genetic crosses. After I fill in all the table, I want another table to be able to count the occurrences of each letter combination.

Essentially, what I want to do is tell a cell to count how many occurrences of "BBHH" are in a range of cells. The COUNTIF function is not case sensitive. I tried nesting the EXACT function inside functions like COUNTIF, COUNT, IF, etc., but EXACT doesn't accept a range of cells.

Is there a way to do this (a.k.a. create a function that would have the same effect as a hypothetical case-sensitive COUNTIF)?

iPad, iOS 7.0.4, 3rd generation

Posted on Nov 18, 2013 2:14 PM

Reply

Answer 1

jaxjason

Level 5

5,063 points

Nov 19, 2013 5:57 AM in response to matt cms

I know that the BBHH is the actual value you need to report, but is it possible to use subtitution characters to make the patterns unique in a new, hidden column? That would possibly solve it while leaving the proper answer in place for printing.

Jason

Reply

Answer 2

matt cms Author

Level 2

368 points

Nov 19, 2013 10:52 AM in response to jaxjason

A function that substituted cell values would still need to be case sensitive.

Reply

Answer 3

jaxjason

Level 5

5,063 points

Nov 19, 2013 11:06 AM in response to jaxjason

I'm talking when you fill in the table. You can enter your own code (XWYZ) of substitute letters, a formula in one cell would do what was needed to convert to the proper (BBHH) code and you could do Countif on the original custom coded column.

Jason

Reply

Answer 4

matt cms Author

Level 2

368 points

Nov 20, 2013 9:15 AM in response to jaxjason

Yes, I could do that, but it would be nice for COUNTIF to be case-sensitive. Thanks, though.

Reply

Answer 5

jaxjason

Level 5

5,063 points

Nov 20, 2013 12:38 PM in response to matt cms

I dont think any spreadsheet program right now does case sensitive. In my office we have written custom VBA functions that show up in excel to accomplish this. Not an option in Numbers.

Jason

Reply

Answer 6

t quinn

Level 5

7,063 points

Nov 27, 2013 11:52 PM in response to matt cms

Matt,

I think you are giving up too soon. Create an additional column that tests each cell for your value using EXACT. Column A has your values, B2 has EXACT("BBHH",A2) and you have filled down as far as you have needed (say B11). In A12 use COUNTIF(B2:B11,"=true") to count matched values. Hide column B. Repeat for each value you want to search for.

Quinn

Reply

Answer 7

matt cms Author

Level 2

368 points

Dec 2, 2013 6:47 PM in response to t quinn

Thanks t quinn,

I tried this, only multiple times for multiple things that I wanted to find.

It worked. Thanks a lot!

Reply

Answer 8

t quinn

Level 5

7,063 points

Dec 2, 2013 9:09 PM in response to matt cms

Matt,

Watching the big boys, they always seem to add a column (or a table) to simplify their formulas. I suspect it speeds things up. Glad it worked for you.

Quinn

Reply