Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-10-2020, 05:09 PM
Marcia's Avatar
Marcia Marcia is offline Count number of Ms between Hs in a column Windows 7 32bit Count number of Ms between Hs in a column Office 2013
Expert
Count number of Ms between Hs in a column
 
Join Date: May 2018
Location: Philippines
Posts: 280
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default 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.
Reply With Quote
  #2  
Old 05-11-2020, 12:13 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count number of Ms between Hs in a column Windows 7 64bit Count number of Ms between Hs in a column Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #3  
Old 05-11-2020, 12:54 AM
Marcia's Avatar
Marcia Marcia is offline Count number of Ms between Hs in a column Windows 7 32bit Count number of Ms between Hs in a column Office 2013
Expert
Count number of Ms between Hs in a column
 
Join Date: May 2018
Location: Philippines
Posts: 280
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
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.
Reply With Quote
  #4  
Old 05-11-2020, 03:39 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count number of Ms between Hs in a column Windows 7 64bit Count number of Ms between Hs in a column Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #5  
Old 05-11-2020, 05:49 AM
Marcia's Avatar
Marcia Marcia is offline Count number of Ms between Hs in a column Windows 7 32bit Count number of Ms between Hs in a column Office 2013
Expert
Count number of Ms between Hs in a column
 
Join Date: May 2018
Location: Philippines
Posts: 280
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
  #6  
Old 05-12-2020, 09:03 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count number of Ms between Hs in a column Windows 7 64bit Count number of Ms between Hs in a column Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Like this ?
Attached Files
File Type: xlsx Counting m.xlsx (9.2 KB, 4 views)
Reply With Quote
  #7  
Old 05-12-2020, 06:21 PM
Marcia's Avatar
Marcia Marcia is offline Count number of Ms between Hs in a column Windows 7 32bit Count number of Ms between Hs in a column Office 2013
Expert
Count number of Ms between Hs in a column
 
Join Date: May 2018
Location: Philippines
Posts: 280
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count number of Ms between Hs in a column 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 number of Ms between Hs in a column 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 number of Ms between Hs in a column count w and write into another column in excel tomlam Excel 5 10-07-2012 06:09 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 01:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft