Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-13-2020, 01:18 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default Trying to count the lines in a grouping

Hi all



I am trying to find a formula that would get a count of the amount of lines in a particular group. Please see the example attached. I would like column "B" to be a formula that does the count. Any assistance would be greatly appreciated.
Attached Files
File Type: xlsx Example.xlsx (9.0 KB, 7 views)
Reply With Quote
  #2  
Old 06-13-2020, 02:01 PM
Logit Logit is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Code:
=count(b:b)
Reply With Quote
  #3  
Old 06-13-2020, 02:46 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

Thanks Logit. But, unfortunately, that doesn't work.
Reply With Quote
  #4  
Old 06-13-2020, 03:01 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

Actually, I may have spoken incorrectly about what I was trying to do. Column B is not populated, I want that column to be populated with a formula that will then populate the field the way it looks in the example. For instance, Group 111 has 4 lines, I want column B to count those rows but not as a total. Line one of that group should be 1, line 2 of that group should be 2 etc. Then the count starts at 1 again when there is a new group. I hope that helps.
Reply With Quote
  #5  
Old 06-13-2020, 03:49 PM
Purfleet Purfleet is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

How about

=COUNTIFS($A$2:A2,A2)
Attached Files
File Type: xlsx Example_purfleet.xlsx (9.7 KB, 5 views)
Reply With Quote
  #6  
Old 06-13-2020, 03:58 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

Thanks Purfleet, I kind of worked, but it didn't restart the count once a new group began.
Reply With Quote
  #7  
Old 06-13-2020, 04:00 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

My sincerest apologies Purfleet. The formulas worked perfectly. I typed it in incorrectly. Thanks again for your help.
Reply With Quote
  #8  
Old 06-13-2020, 04:03 PM
Purfleet Purfleet is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

no problem
Reply With Quote
  #9  
Old 06-13-2020, 05:34 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

ugghhh...The document I am working on has over 75k lines. When I said it worked perfectly I had not checked further down the list. Further down the list for some strange reason the formula stopped working and I am not sure why.
Reply With Quote
  #10  
Old 06-13-2020, 05:39 PM
Purfleet Purfleet is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

Can you upload the workbook?
Reply With Quote
  #11  
Old 06-13-2020, 05:48 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

Unfortunately I cannot. It would violate HIPPA
Reply With Quote
  #12  
Old 06-13-2020, 05:52 PM
Purfleet Purfleet is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

an example? It doesnt have to be the proper data


I cant see why it wouldnt work
Reply With Quote
  #13  
Old 06-13-2020, 06:41 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

I attached a sample of what I am doing. It seemed to be working until it got to line 138. It would correct itself and work for more lines than go off again. See the following lines:

152
156
304
358
364
368
548-551
742-743
4374-4397
Attached Files
File Type: xlsx Sample.xlsx (196.2 KB, 5 views)
Reply With Quote
  #14  
Old 06-13-2020, 06:43 PM
wheddingsjr wheddingsjr is offline Trying to count the lines in a grouping Windows 7 64bit Trying to count the lines in a grouping Office 2016
Advanced Beginner
Trying to count the lines in a grouping
 
Join Date: Mar 2017
Posts: 76
wheddingsjr is on a distinguished road
Default

the count should actually be in coordination with the TRUTH/FALSE column. The TRUTH/FALSE determines when the next group starts.
Reply With Quote
  #15  
Old 06-13-2020, 07:15 PM
Purfleet Purfleet is offline Trying to count the lines in a grouping Windows 10 Trying to count the lines in a grouping Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

It is becuase of the size of the number - excel can only deal with (i think) 15 significant figures and anything else is rounded.


For exmaple if you put 377540323190869743517.9993055556+0 in an other cell it will show as 3.7754e+20, then increase decimals to the max and you will see the cell is actually showing 3.775403231908690000000e+20..


Drop off the zeros and filter for 377540323190869 and you will get 16, which is how many the count is counting.


Now to get around that.......
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Grouping Lines by Project number on a separate page taholmes160 Excel 2 04-17-2019 03:57 AM
Trying to count the lines in a grouping How to count words in a text, arrange them in separate lines and put the grammar class ? Marcway Word 6 12-14-2018 04:25 PM
Trying to add space between lines of bulleted text and a new header but both lines are moving??? Martin_d35 Word 2 02-10-2017 07:13 AM
Trying to count the lines in a grouping Getting the Count formula to count all rows Jennifer Murphy Word Tables 11 08-23-2016 09:37 PM
Trying to count the lines in a grouping Pivot table grouping problem 2 tables need different grouping differentdrummer Excel 3 12-10-2013 01:19 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:18 PM.


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