Microsoft Office Forums Count number of Ms between Hs in a column
 Register FAQ Search Today's Posts Mark Forums Read

#1
05-10-2020, 05:09 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 473
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
05-11-2020, 12:13 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,577

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
05-11-2020, 12:54 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 473

Quote:
 Originally Posted by Pecoflyer 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
The formula returned 0 in B2 instead of 2, then in B5, the result is 2 instead of 1.
It should count the Ms below the H.
#4
05-11-2020, 03:39 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,577

Quote:
 B2 should be 2
Please explain? A2 is M so the cell must stay empty?
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
05-11-2020, 05:49 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 473

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
05-12-2020, 09:03 AM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,577

Like this ?
Attached Files
 Counting m.xlsx (9.2 KB, 6 views)
__________________
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
05-12-2020, 06:21 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 473

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 Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Kubi Excel 2 08-06-2017 08:54 PM Jelmer Excel 6 05-16-2017 05:24 AM ann.acornacchio Excel 4 12-31-2016 05:40 PM fibbermcghee Excel 2 12-09-2014 05:48 PM tomlam Excel 5 10-07-2012 06:09 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:17 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top