Array Question

D

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?
 
B

Bob Phillips

Do you mean

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


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
D

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

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)
 
P

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
 
B

Bob Phillips

It wasn't an array formula.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Top