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.
|