Help with Sumproduct with Indirect

R

Rob

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

JE McGimpsey

One way:

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

convert ranges to INDIRECTs if necessary.
 
A

Arvi Laanemets

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
 
J

JE McGimpsey

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.
 
A

Arvi Laanemets

Hi


JE McGimpsey said:
The only advantage I can see to INDIRECT is that it allows one to insert
columns/rows without the formula automatically adjusting.
Click to expand...

Then I would use dynamic named ranges instead.


Arvi Laanemets
 
J

JE McGimpsey

Arvi Laanemets said:
Then I would use dynamic named ranges instead.
Click to expand...

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

Harlan Grove

Arvi Laanemets wrote...
will work in same way. Even better will be
=SUMPRODUCT(--($E$1:$AN$1<>"Cat"),($E$3:$AN$3))
Click to expand...
....

Better still would be

=SUMIF($E$1:$AN$1 said:
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<>"Cat"),($E$3:$AN$3))
Click to expand...
....

Perhaps you meant '=' rather than '<>'.
 
Top