Excel counts 2 digit numbers as 1...

[Brile]

Hi,

How do I get around Excel counting the first digit in 2 digit numbers, for
example 10, as 1 when I have added a SUMIF<= function. Example: I have 12
different columns, each named TX1, TX2 etc. And a formula that extracts from
a different tab and counts the value if it is <= (less than or equal to) the
current column. When Excel reaches to columns with 2 digit numbers it starts
counting the 1 in the name TX10, and the 1 in the name TX11. How do I get
around this?

 

[Pete_UK]

I don't quite understand this - perhaps you could post examples of
your data and the formulae you are using.

Pete

 

[Charles Williams]

Its because Excel is treating the TX1, TX2 as text and TX11 is less than TX2
(sort the column ascending to see the comparison sequence).

You can either make all the TX1 to TX9 into TX01 to TX09 (make all the TX
strings the same length)

or use a helper column (C) on Sheet 2 to extract the numbers
=VALUE(RIGHT(A1,LEN(A1)-2))

and then change your SUMIF to
=SUMIF(Sheet2!$C$1:$C$30,"<=" &
RIGHT(Sheet1!A1,LEN(A1)-2),Sheet2!$B$1:$B$30)


regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Brile]

Hi Pete,

thanks for answering, I do not know how well you know Excel but if you need
further explainations please ask, I am VERY grateful for help!

Below is my formula,

=SUMIF('Jira Reference'!$J:$J;"<="&L5;'Jira Reference'!$L:$L)/3600

The 'Jira Reference' is the Tab name which I am reffering to. I have in my
first Tab several columns named TX1, TX2 etc. They should with my formula
retrieve data from one column in the Tab 'Jira Reference'. The column in the
'Jira Reference' includes data called TX1, TX2 etc. and the function should
use less than or equal to sum up for example in TX9 also the TX8, TX7, TX6
data etc. And it does, but when Excel comes to compare data for TX10 (2
digits 1 and 0), it does not, it interpretate it as the first digit, 1, and
only counts TX1 and TX0 as less than or equal to.

If I am unclear let me know again, as I said very thankful for help.

PS. I do not know how to post an excel file for example here on the forum? DS.

 

[Brile]

Hi Charles,

Thanks for the help, it is working out fine actually. Have a great weekend!

 

[Brile]

Hi,

Do you possibly have the answer to my other posting "Add several functions
in one formula"?