Sumproduct

[Steved]

Hello From Steved

=SUMPRODUCT(--(Passengers!$A$17:$A$17=$A36),Passengers!
$D$17:$D$17)

The above formula works well but when I change D1717 to
D17:E18 it gives me #VALUE!

Could you please Help me out Here

Thankyou.

 

[Harlan Grove]

=SUMPRODUCT(--(Passengers!$A$17:$A$17=$A36),Passengers!
$D$17:$D$17)

The above formula works well but when I change D1717 to
D17:E18 it gives me #VALUE!

Could you please Help me out Here

Reading online help should always be step 1. It states clearly that

"The array arguments must have the same dimensions. If they do not, SUMPRODUCT
returns the #VALUE! error value."

D17:E17 doesn't have the same dimensions as A17:A17. You can overcome this at
the cost of choking on text in D17:E17 by changing the formula to

=SUMPRODUCT((Passengers!$A$17:$A$17=$A36)*Passengers!$D$17:$E$17)

However, the same result could be calculated much more economically using

=IF(Passengers!$A$17=$A36,SUM(Passengers!$D$17:$E$17),0)

or just

=(Passengers!$A$17=$A36)*SUM(Passengers!$D$17:$E$17)

 

[Steved]

Thankyou Harlan.

-----Original Message-----
...

Reading online help should always be step 1. It states clearly that

"The array arguments must have the same dimensions. If they do not, SUMPRODUCT
returns the #VALUE! error value."

D17:E17 doesn't have the same dimensions as A17:A17. You can overcome this at
the cost of choking on text in D17:E17 by changing the formula to
$D$17:$E$17)

However, the same result could be calculated much more economically using

=IF(Passengers!$A$17=$A36,SUM(Passengers!$D$17:$E$17),0)

or just

=(Passengers!$A$17=$A36)*SUM(Passengers!$D$17:$E$17)