Counting occurrences of text

Hello,

Here is what I would like to do and I am wondering if anyone can help me!

I have a set of data for test results which could be pass or fail - coded as "P" or "F". For each test type (in columns), for each person (in rows), the cell will have P, F, a combination, or may be blank if no test has yet been taken. What I would like to do is have formulas which will tell me:

1. How many people have passed in 1 attempt (i.e. the cell contains only 1 "P")
2. How many people required more than 1 attempt to pass (i.e. cell contains "FFP" or "FP", etc.)
3. How many people have only failed (regardless of how many times - therefore, cell could contain "F" or "FF" or "FFF").
4. How many people have not taken tests (I can probably figure this one out!)

I had previously gotten a formula below:
=SUMPRODUCT(LEN(P6:P31)-LEN(SUBSTITUTE(P6:P31,"P","")))

But this counts how many times "P" occurs in the range, and I want to know how many cells contain only a "P" (passed in 1 attempt), or how many cells contain some fails before a pass (required more than 1 attempt).

I hope someone can figure this out, as I truly do not want to have to count this all manually! Thank you!

Wow, that works great, thank you very much!

Now for a different question that is possibly even more complicated...
So now I can tell if someone required more than 1 attempt to pass the exam, etc. etc. What if I want to know how many people were in each range of marks (i.e. 0-25%, 26-50%, 51-75%, 76%-100%). The problem with this is that I don't want to get too many columns of cells, so I'm just putting all the marks in one column for each person. So for John SMITH (2 tests), the marks might have been 40% and 80% and for Jane SMITH (just 1 test), the mark might have been 95%.
So the table might look a bit like

Name Pass/Fail Test Percent Correct
John FP 40%, 80%
Jane P 95%

Is there a way to tell for all total tests, what percentage of tests were in each mark range? Thanks again for your help!

A piece of advice ...
Do not challenge VBA Noob ...
while I am still reading your question, you will get his answer ...

Carim

Hi,

My recommendation (as the easiest solution) would be a simple pivot table, in which you can group by the different ranges of marks ...
I do not know if VBA Noob will agree ..

HTH
Carim

Originally Posted by pinkgoldfish

Wow, that works great, thank you very much!

Now for a different question that is possibly even more complicated...
So now I can tell if someone required more than 1 attempt to pass the exam, etc. etc. What if I want to know how many people were in each range of marks (i.e. 0-25%, 26-50%, 51-75%, 76%-100%). The problem with this is that I don't want to get too many columns of cells, so I'm just putting all the marks in one column for each person. So for John SMITH (2 tests), the marks might have been 40% and 80% and for Jane SMITH (just 1 test), the mark might have been 95%.
So the table might look a bit like

Name Pass/Fail Test Percent Correct
John FP 40%, 80%
Jane P 95%

Is there a way to tell for all total tests, what percentage of tests were in each mark range? Thanks again for your help!

Without slipping into VBA, I'm not sure it is possible when you have all the test results in the same cell, assuming you want to take into account all test results. If you want to take into account the first test % only, or the highest, or the lowest -- we might be able to come up with something to help you.

I do have one comment though... people generally look at this type of thing for trends and what not. Your data is going to be weighted toward the failures. Ie. If you have someone with FFFFFP and three others with P, you might end up with the following distribution in each % range (from above): 0%-25% -> 0, 26%-50% -> 4, 51%-75% -> 2, 76%-100% -> 2, which may not accurately represent what you're looking for. (ie. someone might look at this sample data and figure people only pass about 50% of the time, whereas 75% of the students pass on first try.)

There are some other possibilities though. You could keep your test results in separate columns in a hidden spreadsheet and use that to create your summary data from. You could also generate your comma separated list of results from this data, so basically you'd just have to add an extra score to the column. Then you could just reference the sheet for data from your summary sheet, which would stay neat and tidy.

Scott

Hello,

Thanks for all the suggestions, I ended up just creating a new worksheet where I can enter that specific data and then used COUNTIF. I have another issue with a macro I'm trying to set up, if you know anything about that you can find the thread under the Excel Programming forum bit. Thanks again!

pinkgoldfish

Hello

I am looking for some help with something similar. It looks less complicated than the example above, yet I still can't work it out.

Does anyone know if it possible to do a count function which looks at the number different phrases within a column.

Eg

I have a column which looks like this:

Smith
Smith
Jones
Jones
Smith
Jones
Brown
Brown
Smith
Jones
Brown

I want to come up with 3 as there are 3 different phrases (Smith, Brown and Jones) within the column.

Thanks

kjb

Thank you so much. It worked!

You're a star!

kjb