sumproduct external reference#2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

If I understand you, this is obvious:
=SUMIF(A1:A100,A12)
=SUMPRODUCT((A1:A100<A12)*(A1:A100>B12))
 
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.
 
Back
Top