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

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

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

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

No.

If I understand you, this is obvious:
=SUMIF(A1:A100,A12)
=SUMPRODUCT((A1:A100<A12)*(A1:A100B12))

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