Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-25-2019, 03:58 PM
mgroessl mgroessl is offline Trying to count the number of cells ending with 001 or 002 Windows 10 Trying to count the number of cells ending with 001 or 002 Office 2016
Novice
Trying to count the number of cells ending with 001 or 002
 
Join Date: Jul 2019
Posts: 4
mgroessl is on a distinguished road
Default Trying to count the number of cells ending with 001 or 002


Could someone please help me? I have a column in my spreadsheet that has all numbers in it, varying from 1 to six digits. I am trying to write a formula that will count how many end with either 001, or 002. However, every time I try that, the zeroes get deleted, which won't work. These numbers have to do with mileage on automobiles. I also tried using "001" and "002", but it tells me I have a bad value. Any help would be appreciated.

Thanks!!!
Reply With Quote
  #2  
Old 07-25-2019, 09:14 PM
Kenneth Hobson Kenneth Hobson is offline Trying to count the number of cells ending with 001 or 002 Windows 10 Trying to count the number of cells ending with 001 or 002 Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

Welcome to the forum!

One method would be to use a helper column. Then count the number of Trues.

=IF(AND(LEN(A2)>=3,VALUE(RIGHT(A2,3))>=1,VALUE(RIG HT(A2,3))<=2),TRUE,FALSE)
Reply With Quote
  #3  
Old 07-26-2019, 12:15 PM
mgroessl mgroessl is offline Trying to count the number of cells ending with 001 or 002 Windows 10 Trying to count the number of cells ending with 001 or 002 Office 2016
Novice
Trying to count the number of cells ending with 001 or 002
 
Join Date: Jul 2019
Posts: 4
mgroessl is on a distinguished road
Default

I probably should have stated that I would need this to cover a range of cells, such as ($R$2:$R$5000). Is that possible? I tried using a countif statement, but that didn't do it.
Reply With Quote
  #4  
Old 07-26-2019, 01:48 PM
mgroessl mgroessl is offline Trying to count the number of cells ending with 001 or 002 Windows 10 Trying to count the number of cells ending with 001 or 002 Office 2016
Novice
Trying to count the number of cells ending with 001 or 002
 
Join Date: Jul 2019
Posts: 4
mgroessl is on a distinguished road
Default

Kenneth Hobson, I believe I have figured out what you meant, and it works perfectly!! Thank you, VERY MUCH!!!
Reply With Quote
  #5  
Old 08-06-2019, 03:09 AM
Debaser's Avatar
Debaser Debaser is offline Trying to count the number of cells ending with 001 or 002 Windows 7 64bit Trying to count the number of cells ending with 001 or 002 Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Without helper column:

=SUMPRODUCT(--(RIGHT($R$2:$R$5000,3)={"001","002"}))
Reply With Quote
  #6  
Old 08-06-2019, 03:39 PM
mgroessl mgroessl is offline Trying to count the number of cells ending with 001 or 002 Windows 10 Trying to count the number of cells ending with 001 or 002 Office 2016
Novice
Trying to count the number of cells ending with 001 or 002
 
Join Date: Jul 2019
Posts: 4
mgroessl is on a distinguished road
Thumbs up Perfect

Quote:
Originally Posted by Debaser View Post
Without helper column:

=SUMPRODUCT(--(RIGHT($R$2:$R$5000,3)={"001","002"}))
Debaser, Thanks!! This works perfectly, without having to rely on the Helper column. That one worked, but this does seem to be more efficient!!

Thanks Again!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to count the number of cells ending with 001 or 002 Count the Number of Cells Containling Dates skeezix Excel 13 05-25-2019 06:11 AM
Trying to count the number of cells ending with 001 or 002 How to Auto Number When document opening is in Column 2 and ending is in Column1 anilsharaf Word 3 09-07-2017 06:27 PM
Trying to count the number of cells ending with 001 or 002 count the number of text in a cell based off a different cells text Kubi Excel 4 08-24-2017 05:53 PM
Trying to count the number of cells ending with 001 or 002 count a number of cells based on the beginning of a order number Kubi Excel 2 08-06-2017 08:54 PM
Count range cells eliminating merge cells danbenedek Excel 0 06-15-2010 12:40 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:31 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