SUMPRODUCT vs Arrays

[Charley Kyd]

I've been using SUM-IF array formulas since Excel 4. But recently, I've
tried to use SUMPRODUCT instead.

That is...
{=SUM(IF((Name="Smith")*(State="Utah"),Amount,0))}
....does the same thing as...
=SUMPRODUCT(0+(Name="Smith"),0+(State="Utah"),Amount)
....but without the inconvience of arrays.

However, today I realized that if Amount is wider than one column,
SUMPRODUCT fails but SUM-IF works.

Am I mising something? Is there a simple way to make SUMPRODUCT work when
Amount is a dynamic range that could define any number of columns?

Charley

 

[Harlan Grove]

=SUMPRODUCT(0+(Name="Smith"),0+(State="Utah"),Amount) ....
However, today I realized that if Amount is wider than one column,
SUMPRODUCT fails but SUM-IF works.

Am I mising something? Is there a simple way to make SUMPRODUCT work
when Amount is a dynamic range that could define any number of columns?

=SUMPRODUCT((Name="Smith")*(State="Utah")*Amount)

 

[T. Valko]

Try it like this:

=SUMPRODUCT((name="smith")*(state="utah")*amount)

Biff

 

[Charley Kyd]

Great! Thanks.

Charley

=SUMPRODUCT((Name="Smith")*(State="Utah")*Amount)