use more than 30 arguments in sum function in exce

问题:

I want to use more than 30 arguments in sum formula for excel, I am using below formula:-

=SUM(IF(AND(ISNUMBER($F$73),ISNUMBER($J$73)),PRODUCT($F$73,$J$73/100),0),IF(AND(ISNUMBER($G$74),ISNUMBER($J$74)),PRODUCT($G$74,$J$74),0))

Above formula will work fine for 30 argumnets, but for more than 30 argumemts excel will return error(#VALUE)

回答1:

You could batch smaller groups into sub-SUMs like this: =SUM(SUM(1,2), SUM(3,4),...).

回答2:

This version will be shorter, you can add as many as you want, only limit will be maximum formula length

=(COUNT(F73,J73)=2)*(F73*J73/100)+(COUNT(G74,J74)=2)*(G74*J74/100)

回答3:

Perhaps you could save further characters in your formula like this:

=(N(F73)*N(J73)+N(G74)*N(J74))/100

which could be extended much further, but for clarity, I'd suggest writing a custom UDF:

=SPRange((F73,G74),(J73,J74))%

SPRange would work like SUMPRODUCT but operates on multiple ranges:

Function SPRange(Range1 As Range, Range2 As Range) As Double

Dim i As Long, n As Long, rng As Range, Arr() As Double

n = Range1.Count
ReDim Arr(1 To n) As Double

i = 1
For Each rng In Range1
    If IsNumeric(rng.Value2) Then Arr(i) = rng.Value2
    i = i + 1
Next rng

i = 1
SPRange = 0
For Each rng In Range2
    If IsNumeric(rng.Value2) Then SPRange = SPRange + Arr(i) * rng.Value2
    i = i + 1
Next rng

End Function

If you want to extend to many ranges you can define names through code:

names.Add "Range1",Range("f73,g74")
names.Add "Range2",Range("j73,j74")

which allows for over 1000 cells in my tests. Then just enter the formula as:

=SPRange(Range1,Range2)%