Array Question

[David Lipetz]

How do you use the SUM function in an array?

For instance, here is array formula that does not work:

=IF($E5:$E398="5N",$K5:$K398,SUM($G5:$J5))

What I need is the array to SUM $G5:$J5 through $G398:$J398

How does one correctly specify this sum range in an array?

 

[Bob Phillips]

Do you mean

=SUMPRODUCT(($E5:$E398="5N")*($G5:$J5))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[David Lipetz]

I don't think so.

Formula should:

For range E5:E398, if E = "5N" then display value in K else sum values in G
through J.

SUMPRODUCT does not seem to do that.

 

[Bob Phillips]

Perhaps this is it

=SUMIF($E5:$E398,"5N",$K5:$K398)+SUMPRODUCT(($E5:$E398<>"5N")*($G5:$J398))


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Pete_UK]

Presumably you used CTRL-SHIFT-ENTER to commit the formula, rather than
just <enter>? And then copied it down to row 398?

Pete

 

[Bob Phillips]

It wasn't an array formula.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Pete_UK]

My question was to the OP, Bob.

Pete