SUMPRODUCT Adding Flexibility

[Paul Corrado]

Taking the formula

=SUMPRODUCT((A1:A15=20)*(B1:B15))

I would like to add the ability to vary the "=" sign to be >, = or <

Is there a way to accomplish this within this formula. I can use a Choose
and a Lookup to incorporate multiple versions into my report, but would
rather be able to flex this character based on user input.

TIA

PC

 

[Alain CROS]

Hi.

Put this code in a module.

Function SumProdSpecialPaul(Range1 As Range, Range2 As Range, Operateur As Range)
Application.Volatile
On Error Resume Next
SumProdSpecialPaul = Evaluate("=SUMPRODUCT((" & Range1.Address & _
Operateur.Value & "20)*(" & Range2.Address & "))")
End Function

G2 = "="
In a cell
=SumProdSpecialPaul(A1:A15;B1:B15;G2)

Alain CROS.

 

[Peo Sjoblom]

Unfortunately there is no other way than using lookup and choose
method that you already have done, the only other way would be
a UDF

 

[Harlan Grove]

Taking the formula

=SUMPRODUCT((A1:A15=20)*(B1:B15))

I would like to add the ability to vary the "=" sign
to be >, = or <

....

I must be missing something. If you had defined names Val referring to 20
and Op referring to "=", then

=SUMIF(A1:A15,OP&Val,B1:B15)

should work.

 

[Paul Corrado]

Harlan,

I only showed part of the formula. The one I am working with has multiple
arguments, not just the one.

PC

 

[Don Guillett]

a replace macro???

 

[Harlan Grove]

I only showed part of the formula. The one I am working with has multiple
arguments, not just the one. ....

....

I kinda figured that. More details in the initial specs helps prevent false
starts.

*IF* your data ranges are either single column-multiple row ranges (or
arrays), then the following is equivalent to =SUMIF(A1:A15,Op&Val,B1:B15).

=SUMPRODUCT((SIGN(A1:A15-Val)=LOOKUP(Op,OpArray,SgnNE)*{1,0}
+LOOKUP(Op,OpArray,SgnEQ)*{0,1})*B1:B15)

where the defined names are

OpArray
={"<","<=","<>","=",">",">="}

SgnNE
={-1,-1,-1,9,1,1}

SgnEQ
={9,0,1,0,9,0}

A Sign call is unavoidable. Also, I believe two Lookup calls are
unavoidable. This uses only one level of nested function calls, but it's
rather long.