software

SUMIF allows one to enter a [valid] criteria text string into cell D2, say
quot;gt;14000quot;, 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

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

--
Don Guillett
SalesAid Software

quot;John Vquot; gt; wrote in message
...
gt; SUMIF allows one to enter a [valid] criteria text string into cell D2, say
gt; quot;gt;14000quot;, and then reference D2 as the function criteria, e.g.,
gt; =SUMIF(B5:B10,D2,C5:C10).
gt;
gt; Is there a syntax that SUMPRODUCT will accept that does the same thing?
gt; Note
gt; the inclusion of a comparison operator is important.
gt;
gt; Thanks! JV
I think he want D2 to hold the = as well Don, or say quot;gt;1400quot;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Don Guillettquot; gt; wrote in message
...
gt; of course,
gt; =sumproduct((a2:a22=d2
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;John Vquot; gt; wrote in message
gt; ...
gt; gt; SUMIF allows one to enter a [valid] criteria text string into cell D2,
say
gt; gt; quot;gt;14000quot;, and then reference D2 as the function criteria, e.g.,
gt; gt; =SUMIF(B5:B10,D2,C5:C10).
gt; gt;
gt; gt; Is there a syntax that SUMPRODUCT will accept that does the same thing?
gt; gt; Note
gt; gt; the inclusion of a comparison operator is important.
gt; gt;
gt; gt; Thanks! JV
gt;
gt;

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--
EdMac
------------------------------------------------------------------------
EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
View this thread: www.excelforum.com/showthread...hreadid=532224Thanks 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 quot;hard-wiredquot;.

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 lt;,gt;,lt;gt;,=, 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

quot;EdMacquot; wrote:

gt;
gt; Have a look at
gt;
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; This will tell you just about all there is to know about this function
gt;
gt; Ed
gt;
gt;
gt; --
gt; EdMac
gt; ------------------------------------------------------------------------
gt; EdMac's Profile: www.excelforum.com/member.php...oamp;userid=30736
gt; View this thread: www.excelforum.com/showthread...hreadid=532224
gt;
gt;

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 gt; 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 amp; 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)

quot;John Vquot; gt; wrote in message
news
gt; Thanks EdMac. I had spent a lot of time on that webpage, and you're right,
it
gt; is excellent. However, I could find no example of where the comparison
gt; operator was not quot;hard-wiredquot;.
gt;
gt; I would like to use cell references so the user could specify the desired
gt; operator without altering the function itself. I suppose I could have a
gt; complicated IF statement that looked for occurences of lt;,gt;,lt;gt;,=, etc. and
gt; then performed the appropriate SUMPRODUCT function. But I was hoping a
more
gt; elegant solution had been found.
gt;
gt; Hope this is clearer, and thanks. JV
gt;
gt; quot;EdMacquot; wrote:
gt;
gt; gt;
gt; gt; Have a look at
gt; gt;
gt; gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt;
gt; gt; This will tell you just about all there is to know about this function
gt; gt;
gt; gt; Ed
gt; gt;
gt; gt;
gt; gt; --
gt; gt; EdMac
gt; gt; ------------------------------------------------------------------------
gt; gt; EdMac's Profile:
www.excelforum.com/member.php...oamp;userid=30736
gt; gt; View this thread:
www.excelforum.com/showthread...hreadid=532224
gt; gt;
gt; gt;
US DF? What's that? I meant a UDF.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; You could always build a US DF that will evaluate it, and use that within
SP
gt;
gt; '---------------------------------------------------------------------
gt; Function fnEval(rng As Range, condition As Range) As Variant
gt; '---------------------------------------------------------------------
gt; Dim cell As Range, row As Range
gt; Dim i As Long, j As Long
gt; Dim aryValues As Variant
gt;
gt; If rng.Areas.Count gt; 1 Then
gt; fnEval = CVErr(xlErrValue)
gt; Exit Function
gt; End If
gt;
gt; If rng.Cells.Count = 1 Then
gt; aryValues = rng
gt; Else
gt; aryValues = rng.Value
gt; i = 0
gt; For Each row In rng.Rows
gt; i = i 1: j = 0
gt; For Each cell In row.Cells
gt; j = j 1
gt; aryValues(i, j) = rng.Parent.Evaluate(cell amp; condition)
gt; Next cell
gt; Next row
gt; End If
gt;
gt; fnEval = aryValues
gt;
gt; End Function
gt;
gt; =SUMPRODUCT(--(fnEval(B1:B10,D5))
gt;
gt;
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;John Vquot; gt; wrote in message
gt; news
gt; gt; Thanks EdMac. I had spent a lot of time on that webpage, and you're
right,
gt; it
gt; gt; is excellent. However, I could find no example of where the comparison
gt; gt; operator was not quot;hard-wiredquot;.
gt; gt;
gt; gt; I would like to use cell references so the user could specify the
desired
gt; gt; operator without altering the function itself. I suppose I could have a
gt; gt; complicated IF statement that looked for occurences of lt;,gt;,lt;gt;,=, etc.
and
gt; gt; then performed the appropriate SUMPRODUCT function. But I was hoping a
gt; more
gt; gt; elegant solution had been found.
gt; gt;
gt; gt; Hope this is clearer, and thanks. JV
gt; gt;
gt; gt; quot;EdMacquot; wrote:
gt; gt;
gt; gt; gt;
gt; gt; gt; Have a look at
gt; gt; gt;
gt; gt; gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt; gt;
gt; gt; gt; This will tell you just about all there is to know about this function
gt; gt; gt;
gt; gt; gt; Ed
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; EdMac
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; EdMac's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=532224
gt; gt; gt;
gt; gt; gt;
gt;
gt;
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.

quot;Bob Phillipsquot; wrote:

gt; US DF? What's that? I meant a UDF.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Bob Phillipsquot; gt; wrote in message
gt; ...
gt; gt; You could always build a US DF that will evaluate it, and use that within
gt; SP
gt; gt;
gt; gt; '---------------------------------------------------------------------
gt; gt; Function fnEval(rng As Range, condition As Range) As Variant
gt; gt; '---------------------------------------------------------------------
gt; gt; Dim cell As Range, row As Range
gt; gt; Dim i As Long, j As Long
gt; gt; Dim aryValues As Variant
gt; gt;
gt; gt; If rng.Areas.Count gt; 1 Then
gt; gt; fnEval = CVErr(xlErrValue)
gt; gt; Exit Function
gt; gt; End If
gt; gt;
gt; gt; If rng.Cells.Count = 1 Then
gt; gt; aryValues = rng
gt; gt; Else
gt; gt; aryValues = rng.Value
gt; gt; i = 0
gt; gt; For Each row In rng.Rows
gt; gt; i = i 1: j = 0
gt; gt; For Each cell In row.Cells
gt; gt; j = j 1
gt; gt; aryValues(i, j) = rng.Parent.Evaluate(cell amp; condition)
gt; gt; Next cell
gt; gt; Next row
gt; gt; End If
gt; gt;
gt; gt; fnEval = aryValues
gt; gt;
gt; gt; End Function
gt; gt;
gt; gt; =SUMPRODUCT(--(fnEval(B1:B10,D5))
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;John Vquot; gt; wrote in message
gt; gt; news
gt; gt; gt; Thanks EdMac. I had spent a lot of time on that webpage, and you're
gt; right,
gt; gt; it
gt; gt; gt; is excellent. However, I could find no example of where the comparison
gt; gt; gt; operator was not quot;hard-wiredquot;.
gt; gt; gt;
gt; gt; gt; I would like to use cell references so the user could specify the
gt; desired
gt; gt; gt; operator without altering the function itself. I suppose I could have a
gt; gt; gt; complicated IF statement that looked for occurences of lt;,gt;,lt;gt;,=, etc.
gt; and
gt; gt; gt; then performed the appropriate SUMPRODUCT function. But I was hoping a
gt; gt; more
gt; gt; gt; elegant solution had been found.
gt; gt; gt;
gt; gt; gt; Hope this is clearer, and thanks. JV
gt; gt; gt;
gt; gt; gt; quot;EdMacquot; wrote:
gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Have a look at
gt; gt; gt; gt;
gt; gt; gt; gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt; gt; gt;
gt; gt; gt; gt; This will tell you just about all there is to know about this function
gt; gt; gt; gt;
gt; gt; gt; gt; Ed
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; EdMac
gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; EdMac's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=532224
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;John Vquot; gt; wrote in message
...
gt; Heh Heh. I was too wimpy to ask, then eventually pieced it together.
Thanks
gt; for your help. Also, I enjoy reading what is, to my eye, elegant code.
Wish I
gt; had more of your skill set.
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; US DF? What's that? I meant a UDF.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Bob Phillipsquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; You could always build a US DF that will evaluate it, and use that
within
gt; gt; SP
gt; gt; gt;
gt; gt; gt; '---------------------------------------------------------------------
gt; gt; gt; Function fnEval(rng As Range, condition As Range) As Variant
gt; gt; gt; '---------------------------------------------------------------------
gt; gt; gt; Dim cell As Range, row As Range
gt; gt; gt; Dim i As Long, j As Long
gt; gt; gt; Dim aryValues As Variant
gt; gt; gt;
gt; gt; gt; If rng.Areas.Count gt; 1 Then
gt; gt; gt; fnEval = CVErr(xlErrValue)
gt; gt; gt; Exit Function
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; If rng.Cells.Count = 1 Then
gt; gt; gt; aryValues = rng
gt; gt; gt; Else
gt; gt; gt; aryValues = rng.Value
gt; gt; gt; i = 0
gt; gt; gt; For Each row In rng.Rows
gt; gt; gt; i = i 1: j = 0
gt; gt; gt; For Each cell In row.Cells
gt; gt; gt; j = j 1
gt; gt; gt; aryValues(i, j) = rng.Parent.Evaluate(cell amp;
condition)
gt; gt; gt; Next cell
gt; gt; gt; Next row
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; fnEval = aryValues
gt; gt; gt;
gt; gt; gt; End Function
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(fnEval(B1:B10,D5))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;John Vquot; gt; wrote in message
gt; gt; gt; news
gt; gt; gt; gt; Thanks EdMac. I had spent a lot of time on that webpage, and you're
gt; gt; right,
gt; gt; gt; it
gt; gt; gt; gt; is excellent. However, I could find no example of where the
comparison
gt; gt; gt; gt; operator was not quot;hard-wiredquot;.
gt; gt; gt; gt;
gt; gt; gt; gt; I would like to use cell references so the user could specify the
gt; gt; desired
gt; gt; gt; gt; operator without altering the function itself. I suppose I could
have a
gt; gt; gt; gt; complicated IF statement that looked for occurences of lt;,gt;,lt;gt;,=,
etc.
gt; gt; and
gt; gt; gt; gt; then performed the appropriate SUMPRODUCT function. But I was hoping
a
gt; gt; gt; more
gt; gt; gt; gt; elegant solution had been found.
gt; gt; gt; gt;
gt; gt; gt; gt; Hope this is clearer, and thanks. JV
gt; gt; gt; gt;
gt; gt; gt; gt; quot;EdMacquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Have a look at
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; This will tell you just about all there is to know about this
function
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Ed
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; EdMac
gt; gt; gt; gt;
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; EdMac's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=30736
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=532224
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;