Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct external reference#2
Similar to previous query. Is there anyway to include an operator in an
external reference to a sumproduct formula. For example, if the formula is =sumproduct((A1:A10=A12)*(B1:B10=B12)) is there any way to have <6 in A12 or 8 in B12? I have figured out how to include multiple single values but not sure if an operator as described can exist in a reference cell (probably not since it doesn't seem to work; but is there a way to do it?) thanks anand |
#2
|
|||
|
|||
Don't think so.
-- HTH RP (remove nothere from the email address if mailing direct) "anand" wrote in message ... Similar to previous query. Is there anyway to include an operator in an external reference to a sumproduct formula. For example, if the formula is =sumproduct((A1:A10=A12)*(B1:B10=B12)) is there any way to have <6 in A12 or 8 in B12? I have figured out how to include multiple single values but not sure if an operator as described can exist in a reference cell (probably not since it doesn't seem to work; but is there a way to do it?) thanks anand |
#3
|
|||
|
|||
Ola, thank you for trying. It sounds like I can't do what I want. I want to
include the operator (greater than or less than) within the external reference and retain only the range and the external reference within the sumproduct formula. The reason for this is that i need to duplicate the sumproduct formulas (there are hundreds) as a group with minor variations in the filtering dozenes of times. In some cases I may want to look at values below something, other times equal to something else and another time, above another value. If the formula has to retain the operator (greater than, less than, etc) within the sumproduct formula, I will have to adjust each one individually; if i can insert into the external reference, I can make a few changes there and have them included in all the hundreds of sumproduct formulas I'm using. So I'm trying to figure out a way to include the operator within the reference cell. anand "Ola" wrote: No. If I understand you, this is obvious: =SUMIF(A1:A100,A12) =SUMPRODUCT((A1:A100<A12)*(A1:A100B12)) |
#4
|
|||
|
|||
There could be other ways to solve your problem. I might have an
idé...but...send a sample (Picture or a zip-file) in this forum: http://www.excelforum.com/index.php? You are not allowed to do send attatchments in this forum (yet). Ola Sandström |
#5
|
|||
|
|||
No.
If I understand you, this is obvious: =SUMIF(A1:A100,A12) =SUMPRODUCT((A1:A100<A12)*(A1:A100B12)) |
#6
|
|||
|
|||
This is not a forum, its is a newsgroup. By not being a web based forum, we
get faster responses, can handle far more questions quickly. In a newsgroup, the message gets downloaded to our machines (unlike the web forums that just provide a link). So id an attachment is included it takes time and increases the risk of viruses.So they will never be welcome here. -- HTH Bob Phillips "Ola" wrote in message ... There could be other ways to solve your problem. I might have an idé...but...send a sample (Picture or a zip-file) in this forum: http://www.excelforum.com/index.php? You are not allowed to do send attatchments in this forum (yet). Ola Sandström |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct external reference | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Save External Link Values | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |