Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Cell Reference with Range Name | Excel Worksheet Functions | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
indirect function to reference cell on different sheet | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |