+ Reply to Thread
Results 1 to 2 of 2

counting multiple values derived from a if/mid statement

  1. #1
    Registered User
    Join Date
    01-11-2011
    Location
    Missoula, Montana
    MS-Off Ver
    Excel 2010
    Posts
    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. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

    Do you mean?

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

    adjusting range to suit.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1