#1




Count number of Ms between Hs in a column
Hi. Column A is populated with "H" and "M". I would like the below formula in B2 to count the number of Ms between Hs but it returns all the Ms in column A instead of the total count of Ms between Hs.
=IF(A2="H",COUNTIF(A$2:A$100,"M"),"") Col A Col B H  3 M M H  3 M H  3 B2 should be 2, B5 should be 1, B7 should be 0. What do I add to the formula that would return the correct value? Thank you. 
#2




Starting in B2 (if the A range starts in A1) try =IF(A2="H",COUNTIF($A$1:$A1,"M")SUM($B$1:$B1),"")
and pull down 
#3




Quote:
It should count the Ms below the H. 
#4




Quote:
Please post a small sample sheet with expected results. As I said my ranges start in A1 and B1 
#5




I'm sorry my computer can't connect to a wifi.
The range starts in A2 with a value of H. A3 to A5 contain M (3 Ms), A6 is H, A7 is M, A8 is M and A9' again H. The formula in B2 should return 3 because A3 to A5 contain 3 M. B6 should be 2 because B7 and BH has 2 M. 
#6




Like this ?

#7




Thank you Pecoflyer. I did not know that a range could be defined with a nested function (A2:INDEX(......). Yesterday I had to provide the needed data so I resorted to pivot table then copied the result to the required form. But with the formula, I need not leave the sheet.

Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
count a number of cells based on the beginning of a order number  Kubi  Excel  2  08062017 08:54 PM 
Count only numbers in filtered column  Jelmer  Excel  6  05162017 05:24 AM 
Count # of times "text" is in 1 column is specific date is in the other column  ann.acornacchio  Excel  4  12312016 05:40 PM 
Want a quotient using a constant to show up in column c each time a number is added to column b  fibbermcghee  Excel  2  12092014 05:48 PM 
count w and write into another column in excel  tomlam  Excel  5  10072012 06:09 AM 