How to calculate a sequecnce of numbers in a single cell separted by commas and dashes

Is there a way to count numbers in a cell separated by commas but also by a range ( dashes) ? for example:


Tx# (H10) Total (I10)
_____ ____
1,2,3,4,5-10 9


So that a person does not have to enter 1,2,3,4,5,6,7,8,9,10 It would just count the span?


The formula I currently have to calcualte them separated by commas is :

=if(SUMPRODUCT((LEN(H10)-LEN(SUBSTITUTE(H10,",",""))))=0,if(count(H10)=1,sumproduct((len(H10)-LEN(substitute(H10,",",""))+1)),"0"),sumproduct((len(H10)-len(substitute(H10,",",""))+1)))

Hi
Assuming A1 contains the list of numbers you could use this formula:

=(LEN(A1)-LEN(SUBSTITUTE(A1, ",",""))+1)+(LEN(A1)-LEN(SUBSTITUTE(A1, "-","")))

Good luck.
Tony

Hi thanks for responding. I'm honestly a little slow when it comes to this. Would this be an addition to my formula I already have listed?

Hi
No, replace your formula with this one.
Tony

Ok I do not find that it works. It shows the value as one for the sequence

1,3,4,5,9-20,5

Yes. Most likely the numbers in the cell will not duplicate as in my example.. as my staff should be working by sequence in a particular invoice. For example they are working transactions Number 1,3,4,5,9-20
under Invoice Number 5463587


The result should be Total number of Transactions worked should equal to 16 number of transactions worked for that Invoice

No you are correct there are 10. My mistake. I already explained I'm a little slow..lol

I don't know how you know all of this..but Thanks a lot. My last question in regards to this would be how do you make sure the value is zero for boxes that are empty? I put in the formula and it calculated them correctly in B1 lets say..but to total all the transactions at the bottom it's saying Value ..because now my sum formula doesn't work

You're a LIFE SAVER.. Thanks so Much !!! Have a great Day!!

Ok I lied. One last question. When I go down the sheet. For example If the span is 1,2,3,4,5,6,7,8,9,10 and I enter 1-10 it converts it to a date. Anything that starts with 1-12 ( for the 12 calendar months) converts it to a date. Is there a way around this?

I didn't lie about you being a life saver..lol.. just that I only had one more question!