# counting multiple values derived from a if/mid statement

1. ## counting multiple values derived from a if/mid statement

There are ways to create intermediate steps to solve this problem but I want do this as a single formula in a cell.

I have a single column of values that contain some unique values that repeat a couple times. I am interested in one specific value in the string that determines a grouping. This section of the string value is represented in the Mid part of the equation below. These values range from 1 to 9. So I would like to see how many of each one of those mid values are contained in my whole column. I thought that I could create if statements and then count them as solution. My problem is that I I found that to get the below equation to work accurately I would have to enter a new if / mid statement for every row. I have 118 rows and 7 unique values so I am looking for a shortcut, some kind of a loop.

=COUNT(IF(MID(AH504,2,1)="5",1,0),IF(MID(AH505,2,1)="5",1,0),IF(MID(AH506,2,1)="5",1,0))

Thanks,

2. ## Re: counting multiple values derived from a if/mid statement

Do you mean?

=SUMPRODUCT(--(MID(AH504:AH506,2,1)="5"))

There are currently 1 users browsing this thread. (0 members and 1 guests)