SUMPRODUCT Criteria Via Cell Reference??
 

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

SUMPRODUCT Criteria Via Cell Reference??
 

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

--
Don Guillett
SalesAid Software

"John V" wrote in message
...
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

SUMPRODUCT Criteria Via Cell Reference??
 

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)

"Don Guillett" wrote in message
...
of course,
=sumproduct((a2:a22=d2

--
Don Guillett
SalesAid Software

"John V" wrote in message
...
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

SUMPRODUCT Criteria Via Cell Reference??
 

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: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=532224

SUMPRODUCT Criteria Via Cell Reference??
 

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

"EdMac" wrote:


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: http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=532224

SUMPRODUCT Criteria Via Cell Reference??
 

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)

"John V" wrote in message
...
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

"EdMac" wrote:


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:
http://www.excelforum.com/member.php...o&userid=30736
View this thread:
http://www.excelforum.com/showthread...hreadid=532224

SUMPRODUCT Criteria Via Cell Reference??
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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)

"John V" wrote in message
...
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

"EdMac" wrote:


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:
http://www.excelforum.com/member.php...o&userid=30736
View this thread:
http://www.excelforum.com/showthread...hreadid=532224

SUMPRODUCT Criteria Via Cell Reference??
 

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" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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)

"John V" wrote in message
...
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

"EdMac" wrote:


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:
http://www.excelforum.com/member.php...o&userid=30736
View this thread:
http://www.excelforum.com/showthread...hreadid=532224

SUMPRODUCT Criteria Via Cell Reference??
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John V" wrote in message
...
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" wrote:

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" wrote in message
...
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)

"John V" wrote in message
...
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

"EdMac" wrote:


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:
http://www.excelforum.com/member.php...o&userid=30736
View this thread:
http://www.excelforum.com/showthread...hreadid=532224