count a text string in one column up to a change in of data in another column

  • I have an excel list column A has the person ids, column I has a text string of AM or PM I want to add a formulas to the end column (M) to count the number of times AM appears in the AM column for each person id so for example id A09356 is repeated in Column A 100 times it then changes to A09513 I want the formula to count the number of AM entries for A09356 then when this changes to A09513 calculate how many AM entries for A09513 and so on.


    I have attached a file

    Files

    • test.xlsx

      (153.46 kB, downloaded 76 times, last: )
  • Re: count a text string in one column up to a change in of data in another column


    Try in M2:


    [COLOR="#0000FF"]=IF(A1=A2,"",COUNTIFS(A:A,A2,I:I,"AM"))[/COLOR]


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • Re: count a text string in one column up to a change in of data in another column


    that's great thank you I am sure I tried over complicate this, this is greatly appreciated.