array formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an array formula summing only posotive numbers in a
single column but in differant rows. I'm not able to use
the : because it sums all info inbetween the differant
rows together, I've tried a , and a ; but then I get a
reading #VALUE! any ideas how to sum all posotives in a
single column but differant rows?
 
Show us the formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
As you can see it is rather ugly

=SUMPRODUCT(--(LARGE((A1,A3,A5:A7),ROW(INDIRECT("1:"&COUNT(A1,A3,A5:A7))))>0
),LARGE((A1,A3,A5:A7),ROW(INDIRECT("1:"&COUNT(A1,A3,A5:A7)))))


you might as well use multiple sumif

=SUMIF(A1:A1,">0")+SUMIF(A3:A3,">0")+SUMIF(A5:A7,">0")

obviously if the number of cells are large you can name the non contiguous
range, then the first formula can be used

=SUMPRODUCT(--(LARGE(MyRange,ROW(INDIRECT("1:"&COUNT(MyRange))))>0),LARGE((M
yRange),ROW(INDIRECT("1:"&COUNT(MyRange)))))


even if you used a lot of cells the first formula would look like the above
while the multiple sumif would be more ugly repeated 50 times or more
 
=SUM((F2,F12,F13>0)*(F2,F12,F13))
In column F there is like 30 differant rows I need the sum
of, but some of those rows I'm adding together contain
negative values that are related to another worksheet.
That is why I thought maybe the array formula would work,
which if all my rows fell inline with one another this
formula would work using F2:F13, instead though I'm trying
to group for example F2, F12, and F13 to a total of only
the positives in that group.
 
=SUM((F2,F12,F13>0)*(F2,F12,F13))
Click to expand...
...

The fault here is Microsoft's. (F2,F12,F13>0) should be considered a syntax
error that would prevent you from entering the formula in the cell rather than
accept it but return #VALUE!. The main point is that (F2,F12,F13>0) doesn't do
what you seem to intend it to do - generate an array of True/False values
corresponding to whether or not F1, F12 and F13 are positive. There's no easy
way to do this because SUMIF doesn't accomodate multiple area ranges. The best
approach in this case is brute force.

=SUMIF(F2,">0")+SUMIF(F12,">0")+SUMIF(F13,">0")

Anything involving OFFSET or INDIRECT would be a lot more complex and not
necessarily shorter or faster to recalc.
 
Thank you all for all your help.
-----Original Message-----
...
...

The fault here is Microsoft's. (F2,F12,F13>0) should be considered a syntax
error that would prevent you from entering the formula in the cell rather than
accept it but return #VALUE!. The main point is that (F2,F12,F13>0) doesn't do
what you seem to intend it to do - generate an array of True/False values
corresponding to whether or not F1, F12 and F13 are positive. There's no easy
way to do this because SUMIF doesn't accomodate multiple area ranges. The best
approach in this case is brute force.

=SUMIF(F2,">0")+SUMIF(F12,">0")+SUMIF(F13,">0")

Anything involving OFFSET or INDIRECT would be a lot more complex and not
necessarily shorter or faster to recalc.
Click to expand...
 
Back
Top