Member Avatar for 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

  • 3 Contributors
  • 4 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by cguan_77

Dani AI

Generated

Brief recap tied to the thread: is trying to sum many scattered single cells with a single formula and ran into a per-function argument cap; pointed to version differences and offered a macro. Below are practical alternatives that avoid the argument-limit pain and that work in older Excel builds.

Non‑VBA, quick fixes:

  • Create a single named union of noncontiguous cells: hold Ctrl and click each cell to pick them, type a short name in the Name Box (left of the formula bar) and press Enter. Then use =SUM(MyName) — the name is one argument so the function limit is no longer an issue.
  • Use a helper column: place 1 next to cells you want included (or a flag), then sum with =SUMIF(FlagsRange,1,ValuesRange) or =SUMPRODUCT(ValuesRange*(FlagsRange=1)). This is fast, auditable and works without macros.
  • Where possible, group contiguous cells into ranges inside SUM (=SUM(C1:C5,C11:C15,...)) to reduce argument count.

Small UDF alternative (works in any Excel but requires macros enabled):

  • Add a standard module and paste this function, then call it from a sheet like =SumFromList("C1,C5,C11,C13").
Function SumFromList(CellList As String) As Double
    Dim parts As Variant, i As Long
    parts = Split(CellList, ",")
    For i = LBound(parts) To UBound(parts)
        On Error Resume Next
        SumFromList = SumFromList + Range(Trim(parts(i))).Value
        On Error GoTo 0
    Next i
End Function

Notes and gotchas: save as a macro‑enabled workbook and enable macros or the UDF will return #NAME?. If summing across sheets, include sheet names (wrap names with spaces in single quotes inside the string). If values are text or errors, handle or clean them first; helper-column approaches are safer and easier to audit.

Recommended Answers

All 4 Replies

Member Avatar for 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..

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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

;)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.