Help with Sumproduct with Indirect
 

Hi
I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?
Thanks
Rob

One way:

=SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$1:$AN$1<"Dog"),$E$3:$AN$3))

convert ranges to INDIRECTs if necessary.

In article ,
Rob wrote:

I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?

Hi

Why do you use INDIRECT here? Simply
=SUMPRODUCT(($E$1:$AN$1<"Cat")*($E$3:$AN$3))
will work in same way. Even better will be
=SUMPRODUCT(--($E$1:$AN$1<"Cat"),($E$3:$AN$3))

About your question - for which range will the additional criterium apply?
When you want to sum all values in row 3 with "Dog" or "Cat" in row 1, then
=SUMPRODUCT(--($E$1:$AN$1<"Dog"),($E$3:$AN$3))+SUMPRODUCT(--($E$1:$AN$1<"C
at"),($E$3:$AN$3))
When you want to sum all values in row 3 with "Cat" in row 1 and "Dog" in
row 2, then
=SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$2:$AN$2<"Dog"),($E$3:$AN$3))


Arvi Laanemets


"Rob" wrote in message
...
Hi
I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?
Thanks
Rob

The only advantage I can see to INDIRECT is that it allows one to insert
columns/rows without the formula automatically adjusting.

I probably could come up with a scenario where that made sense.

In article ,
"Arvi Laanemets" wrote:

Why do you use INDIRECT here?

Thanks!

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$1:$AN$1<"Dog"),$E$3:$AN$3))

convert ranges to INDIRECTs if necessary.

In article ,
Rob wrote:

I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?

Hi


"JE McGimpsey" wrote in message
...
The only advantage I can see to INDIRECT is that it allows one to insert
columns/rows without the formula automatically adjusting.

Then I would use dynamic named ranges instead.


Arvi Laanemets

In article ,
"Arvi Laanemets" wrote:

Then I would use dynamic named ranges instead.

I might, too, but at the same time, it would introduce a layer of
obfuscation that using INDIRECT() doesn't.