#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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
||||
|
||||
I'm sorry my computer can't connect to a wi-fi.
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 ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#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 | 08-06-2017 08:54 PM |
Count only numbers in filtered column | Jelmer | Excel | 6 | 05-16-2017 05:24 AM |
Count # of times "text" is in 1 column is specific date is in the other column | ann.acornacchio | Excel | 4 | 12-31-2016 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 | 12-09-2014 05:48 PM |
count w and write into another column in excel | tomlam | Excel | 5 | 10-07-2012 06:09 AM |