Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John V
 
Posts: n/a
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
EdMac
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John V
 
Posts: n/a
Default 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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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






  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John V
 
Posts: n/a
Default 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







  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sheet name reference within a cell reference??? Broyston Excel Discussion (Misc queries) 9 July 8th 08 08:35 PM
Cell Reference with Range Name SCSC Excel Worksheet Functions 2 March 24th 06 12:32 AM
why a reference to an empty cell is not considered empty Nicoscot Excel Discussion (Misc queries) 10 March 10th 06 06:36 AM
indirect function to reference cell on different sheet Dolemite Excel Worksheet Functions 2 August 19th 05 05:25 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM


All times are GMT +1. The time now is 11:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"