SUMPRODUCT Criteria Via Cell Reference??

John V · 04-12-2006, 08:10 AM

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 · 04-12-2006, 08:50 AM

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

--
Don Guillett
SalesAid Software
[email protected]
"John V" <[email protected]> wrote in message
news:[email protected]...
> 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** · 04-12-2006, 08:53 AM

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

Bob Phillips · 04-12-2006, 08:55 AM

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" <[email protected]> wrote in message
news:[email protected]...
> of course,
> =sumproduct((a2:a22=d2
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "John V" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>

John V · 04-12-2006, 11:25 AM

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
>
>

Bob Phillips · 04-12-2006, 12:25 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >
> >

Bob Phillips · 04-12-2006, 12:45 PM

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > >
> > >
>
>

John V · 04-12-2006, 02:35 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > > >
> > > >
> >
> >
>
>
>

Bob Phillips · 04-12-2006, 03:00 PM

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John V" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

**murali_madasu** · 07-13-2014, 10:41 AM

Bob,

Your help is greatly apprecited.

Thanks very much for this UDF...This really helped many days of my effort.

This UDF is working for Named ranges if they are numbers only, but I do have named ranges with text content.

Could you please help me to update this function to take care of text named ranges also.

Thanks again,
Murali