SUMPRODUCT Criteria Via Cell Reference??

[Guest]

SUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).

Is there a syntax that SUMPRODUCT will accept that does the same thing? Note
the inclusion of a comparison operator is important.

Thanks! JV

 

[Don Guillett]

of course,
=sumproduct((a2:a22=d2

 

[Bob Phillips]

I think he want D2 to hold the = as well Don, or say ">1400"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

of course,
=sumproduct((a2:a22=d2

--
Don Guillett
SalesAid Software
(e-mail address removed)

SUMIF allows one to enter a [valid] criteria text string into cell D2, say
">14000", and then reference D2 as the function criteria, e.g.,
=SUMIF(B5:B10,D2,C5:C10).

Is there a syntax that SUMPRODUCT will accept that does the same thing?
Note
the inclusion of a comparison operator is important.

Thanks! JV

 

[EdMac]

Have a look at

www.xldynamic.com/source/xld.SUMPRODUCT.html

This will tell you just about all there is to know about this function

Ed

 

[Guest]

Thanks EdMac. I had spent a lot of time on that webpage, and you're right, it
is excellent. However, I could find no example of where the comparison
operator was not "hard-wired".

I would like to use cell references so the user could specify the desired
operator without altering the function itself. I suppose I could have a
complicated IF statement that looked for occurences of <,>,<>,=, etc. and
then performed the appropriate SUMPRODUCT function. But I was hoping a more
elegant solution had been found.

Hope this is clearer, and thanks. JV

 

[Bob Phillips]

You could always build a US+DF that will evaluate it, and use that within SP

'---------------------------------------------------------------------
Function fnEval(rng As Range, condition As Range) As Variant
'---------------------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim aryValues As Variant

If rng.Areas.Count > 1 Then
fnEval = CVErr(xlErrValue)
Exit Function
End If

If rng.Cells.Count = 1 Then
aryValues = rng
Else
aryValues = rng.Value
i = 0
For Each row In rng.Rows
i = i + 1: j = 0
For Each cell In row.Cells
j = j + 1
aryValues(i, j) = rng.Parent.Evaluate(cell & condition)
Next cell
Next row
End If

fnEval = aryValues

End Function

=SUMPRODUCT(--(fnEval(B1:B10,D5))




--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

US+DF? What's that? I meant a UDF.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Heh Heh. I was too wimpy to ask, then eventually pieced it together. Thanks
for your help. Also, I enjoy reading what is, to my eye, elegant code. Wish I
had more of your skill set.

 

[Bob Phillips]

I think I'll add it to the web page.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)