Counting the digits in a range

[MaxPower]

What I am working on is a spreadsheet used for calculating how many labels are needed for RJ-45 patch panels. Each port on the pach panel has a lable comprised of single digit labels Example port # 1 is 001. so that would be two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under it 002 and drag to 500 or whatever number of ports I need. It then tells me on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a start number in one cell like 001 and an end number in another cell say 600, and achieve the same results.

Thank you in advance for anyone who can help.

 

[Bernard Liengme]

Can't follow question. Are you trying to sum 1+2+3......N?
The answer is N(N+1)/2 as proved by Gauss when he was 7 years old

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address

What I am working on is a spreadsheet used for calculating how many labels

are needed for RJ-45 patch panels. Each port on the pach panel has a lable
comprised of single digit labels Example port # 1 is 001. so that would be
two 0 labels and one 1 lable, etc.

I currently am using the autofill feature so that I type in 001 then under

it 002 and drag to 500 or whatever number of ports I need. It then tells me
on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a

start number in one cell like 001 and an end number in another cell say 600,
and achieve the same results.

 

[Bob Phillips]

Max,

Here is a way with putting the start at end numbers in the formula

=SUMPRODUCT(--(MID(TEXT(ROW(1:600),"000"),{1,2,3},1)=A1))

in A1 put the label to be tested for, 0, 1, etc. as text, for instance '0,
'1, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

What I am working on is a spreadsheet used for calculating how many labels

are needed for RJ-45 patch panels. Each port on the pach panel has a lable
comprised of single digit labels Example port # 1 is 001. so that would be
two 0 labels and one 1 lable, etc.

I currently am using the autofill feature so that I type in 001 then under

it 002 and drag to 500 or whatever number of ports I need. It then tells me
on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a

start number in one cell like 001 and an end number in another cell say 600,
and achieve the same results.

 

[Mark Graesser]

Hi Max
Could you post one of the formulas you are using to count the digits. This can probably be set up to start with a table from 001 to 999, then two MATCH functions can be used to select the start and end positions in the table

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- MaxPower wrote: ----

What I am working on is a spreadsheet used for calculating how many labels are needed for RJ-45 patch panels. Each port on the pach panel has a lable comprised of single digit labels Example port # 1 is 001. so that would be two 0 labels and one 1 lable, etc.
I currently am using the autofill feature so that I type in 001 then under it 002 and drag to 500 or whatever number of ports I need. It then tells me on the right how many labels fro 0 to 9 I need to purchase.

What I would like to accomplish is to set it up so that I can put in a start number in one cell like 001 and an end number in another cell say 600, and achieve the same results.

Thank you in advance for anyone who can help.

 

[Mark Graesser]

Very nice formula Bob

I played around with it a little to allow the start and finish numbers to be entered in seperate cells

=SUMPRODUCT(--(MID(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"000"),{1,2,3},1)=A5)

The start number goes in B1, the end number goes in B2 and the number being counted goes in A5

Good Luck Max

Regards
Mark Graesse
(e-mail address removed)
Boston M


----- Bob Phillips wrote: ----

Max

Here is a way with putting the start at end numbers in the formul

=SUMPRODUCT(--(MID(TEXT(ROW(1:600),"000"),{1,2,3},1)=A1)

in A1 put the label to be tested for, 0, 1, etc. as text, for instance '0
'1, etc

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

What I am working on is a spreadsheet used for calculating how many label

are needed for RJ-45 patch panels. Each port on the pach panel has a labl
comprised of single digit labels Example port # 1 is 001. so that would b
two 0 labels and one 1 lable, etc

I currently am using the autofill feature so that I type in 001 then unde

it 002 and drag to 500 or whatever number of ports I need. It then tells m
on the right how many labels fro 0 to 9 I need to purchasestart number in one cell like 001 and an end number in another cell say 600
and achieve the same results

 

[MaxPower]

Sure

This formula is in each of the cells accross for 0,1,2,3,4,5,6,7,8,9 . =LEN(B11)-LEN(SUBSTITUTE(SUBSTITUTE(B11,"0",""),"0",""))

Then on the right I have the "quantity listed verticaly and the following formula in each cell. =SUM(C10:C2508

I have it counting the digits fine, but I just want to set it up to use a "start of range "cell and End of range "cell, instead of the whole fill range process. The ports are numbered using a 3 digit number like 001, 002, and can range from only a few ports per patch panel up to hundreds of ports. Then I may need to do 50 or 100 panels. So, tou see my need to find a better way.

 

[Bob Phillips]

Doh! Of course, I should have thought of that.

Bob

Almost forgot, instead of entering the digit to be counted as text, you

could add a TEXT function to the counted argument:

=SUMPRODUCT(--(MID(TEXT(ROW(INDIRECT($B$1&":"&$B$2)),"000"),{1,2,3},1)=TEXT(
A5,"0")))

Regards,
Mark Graesser
(e-mail address removed)
Boston MA

----- Mark Graesser wrote: -----

Very nice formula Bob!

I played around with it a little to allow the start and finish

numbers to be entered in seperate cells.

 

[Bob Phillips]

Thanks Mark. Funny how you went that way, I started with that and thought it
better to embed it in the formula. Life's rich tapestry I guess. These
posters are so lucky, not only solutions, but variety as well <G>

Bob

 

[Bob Phillips]

Thanks guys, appreciate the comments.

Mark (MaxPower version), it seems that you struck lucky with Mark helping
you through. The Internet is good isn't it?

Bob