counting instances of numbers in a row based on contents of column heading and value

[dcgrove]

Hello, I need to count the number of instances the number 2 and the number 3 appears in each row of an array. However, there are some conditions that need to be met. Please see attached sheet to understand how the data is set up as this is going to be tricky. Each column has a heading consisting of a sequence of numbers and periods (1.1.1, 1.1.2, 1.1.4, 1.2.4, 1.2.5, 1.3.1, 2.1.1, 2.1.2, 3.1.1, 3.1.2 etc.) 1.1.1 is a section of section of 1.1 and 1.1 is a subsection of section 1. The numbers represent critical (3) and major (2) violations of these subheadings and because of the way the violations are scored, only the highest level violation is counted when two or more violations are given in any particular subheading. So if there is a critical violation under section #1.1.1 and a major (2) in section #1.1.2, only the critical (3) violation is counted. I have highlighted some sections of data where there are multiple violations in a subheading.


1. If the first three characters in the column headings are the same, only the first instance of the number is counted.

2. If both numbers appear in congruent cells that meet the first condition only the highest number should be included in the count of "3", while the "2"'s would not be included.