sumproduct variables

Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"}).......

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

Tony

I got it to work by putting the yes, no, maybe in cells
D1 - F1 and using
=SUMPRODUCT((A1:A100=D1:F1)*1)

Tony
-----Original Message-----
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"})..... ..

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I
get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

.

-----Original Message-----
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"})..... ..

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I
get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

.


In the example the cells are a contiguous range but what
if they were not? Say they were D1,E5,F22.

Tony

You could try:

=SUMPRODUCT((A1:A100=D1)+(A1:A100=E5)+(A1:A100=F22 ))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Tony" wrote in message
...

-----Original Message-----
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"})..... ..

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I
get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

.


In the example the cells are a contiguous range but what
if they were not? Say they were D1,E5,F22.

Tony

=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$100,$D1:$F1,0)),...)

"Tony" wrote in message
...
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"}).......

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

"RagDyer" wrote...
You could try:

=SUMPRODUCT((A1:A100=D1)+(A1:A100=E5)+(A1:A100=F2 2))
...

As a perverse alternative if all cells were text,

=SUMPRODUCT(--(A1:A100=T(OFFSET(D1:F22,{0,4,21},{0,1,2),1,1))))

The underlying point is that array constants can hold only numeric, text and
error *constants*. They can't include expressions, e.g., 1+2 and "a"&"b", or
cell references. Multiple area range references won't work in any arithmetic
expressions. They're only useful in functions that accept 3D references plus
INDEX and functions that require range arguments, e.g., CELL's 2nd arg.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.

Thanks guys. I was expecting something different. Maybe it
can't be done the way I think.

Tony
-----Original Message-----
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"})..... ..

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I
get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

.

Huh?

"Tony" wrote in message
...
Thanks guys. I was expecting something different. Maybe it
can't be done the way I think.

Tony
-----Original Message-----
Hello Group,

How do I include multiple cell reference variables in a
sumproduct function? For example, if I wanted to include
text values I would use -

=Sumproduct((A1:A100={"yes","no","maybe"})..... ..

This works but those values are variable and change often
and would require a lot of editing. I could also use each
value in a seperate array but that's not the way to go.


But when I replace "yes" "no" "maybe" with cell refs I
get
the general formula error.

I've tried these -

{D1,E1,F1}
{D1;E1;F1}

Thanks for any help
Tony

.