#1
|
|||
|
|||
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!!! |
#2
|
|||
|
|||
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) |
#3
|
|||
|
|||
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.
|
#4
|
|||
|
|||
Kenneth Hobson, I believe I have figured out what you meant, and it works perfectly!! Thank you, VERY MUCH!!!
|
#5
|
||||
|
||||
Without helper column:
=SUMPRODUCT(--(RIGHT($R$2:$R$5000,3)={"001","002"})) |
#6
|
|||
|
|||
Perfect
Quote:
Thanks Again!! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Count the Number of Cells Containling Dates | skeezix | Excel | 13 | 05-25-2019 06:11 AM |
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 |
count the number of text in a cell based off a different cells text | Kubi | Excel | 4 | 08-24-2017 05:53 PM |
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 |