MS Excel Formula Prob

[Ajitbmsce]

I want to add some different col.no. according to my requirement but Sum () is it taking only 30 individual no. (or 30 parameter) after that it is showing too many paramters
How to add more than 30 individual no. in EXCEL

it's a big prb for me
Reply soon

[cguan_77]

post your question at software development...visual basic..

anyway what you really want to do add columns, rows or what..

can you attach the file that you're trying to do..

[Ajitbmsce]

No, i just want to find sum of more than 30 cell in a particular row or column.

suppose i am having 100 row data. Here i want to calculate the sum of only 40 row that is not necessary in continuous range but in scattered row no.
For ex. c1, c5,c11,c13,15,c23,c24,c25,c27,..... forty cell.
i am using formula =Sum(c1,c5,c11,c13,c15,c23,...........) for this.

then it is showing too many parameters but it is accepting till 30 parameter

[Suspishio]

Excel 2007 has increased this limit from 30 to 255 items in your argument list.

No way of doing it directly with your version of excel.

[cguan_77]

hi ajitbmcse, try this macro.. go to tools -> macro then create a new macro..i tested it up to 50 cells its working.. don't put a double space between each data cell like c1 c2..make sure only one space between them or else there will be an error

here's the code

Sub TotalValue()
    Dim strcells, strsum As String
    Dim Splitval As Variant
    Dim intIndex, totalval As Integer
    Dim cellvalue, strsplit As String
    
strcells = InputBox("Enter the cells you want to be added, enter with spaces like: c1 c2 c10")

strsum = InputBox("Enter the cell where you want to display the total value, enter like: F1")



    Splitval = Split(strcells, " ")
    For intIndex = LBound(Splitval) To UBound(Splitval)
         
       strsplit = Splitval(intIndex)
       cellvalue = Range(strsplit).Value
       totalval = cellvalue + totalval
       Range(strsum).Value = totalval
       
    Next
End Sub

;)