I have a worksheet that measures how many NOs (entered as N) and PARTIALs (entered as P) are marked against an employee via a Scoring Sheet that is used to measure performance. For example, if the Scoring Sheet has 10 Steps, and they received a full NO in Step 1 and PARTIAL in Step 10, I would document "N 1, P 10".
At the end of each month, I would like to begin counting how many times each Step was missed for the entire month, regardless of it was marked as a full NO (N) or PARTIAL (P). So, in other words if someone received 5 separate Scoring Sheets in the month, and out of those 5 Scoring Sheets, they received 3 NOs for Step 1 and 2 PARTIALs for Step 1, I want it recognize both as one and the same, and return a number of 5 total variances for that Step.
Here is an example of a month where six Scoring Sheets were completed, with all the NOs, PARTIALs, and NAs (Non-applicables) were captured:
Ignoring all the other text (such as Overturned, non-urgent, and CoW Nominee), I would like to capture how many times each Step was marked as either a N or P. As you can see, some cells contain both Ns and Ps for different Steps. Ideally, I would have a table such as in the image below that shows how many times each Step was marked as either N or P:
What formula or formulas can I use that will look through each cell in the first image above and count how many times each Step was marked as either N or P?
Thank you!
11 Answer
Use theCOUNTIF function.
Assuming an N followed by a blank is one result, and the same for P,
the formula for your example is:
=COUNTIF(A1:F1,"*N *")+COUNTIF(A1:F1,"*P *")Once it works for row 1, you may drag drown to extend the formula down over the rows below.
Note: The regular expression inside the above quotes can be further
tweaked if other value names may terminate with N or P.
