A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

array formula



 
 
Thread Tools Display Modes
  #1  
Old February 26th, 2004, 06:02 PM
external usenet poster
 
Posts: n/a
Default array formula

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?
  #2  
Old February 26th, 2004, 06:44 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default array formula

Show us the formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

wrote in message
...
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?



  #3  
Old February 26th, 2004, 06:52 PM
Peo Sjoblom
external usenet poster
 
Posts: n/a
Default array formula

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,A 3,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

--

Regards,

Peo Sjoblom


wrote in message
...
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?



  #4  
Old February 26th, 2004, 07:11 PM
external usenet poster
 
Posts: n/a
Default array formula

=SUM((F2,F12,F130)*(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.

-----Original Message-----
Show us the formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

wrote in message
...
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?



.

  #5  
Old February 26th, 2004, 08:41 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default array formula

" wrote...
=SUM((F2,F12,F130)*(F2,F12,F13))

...

The fault here is Microsoft's. (F2,F12,F130) 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,F130) 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.

--
To top-post is human, to bottom-post and snip is sublime.
  #6  
Old April 8th, 2004, 01:35 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default Array Formula

Hi
try
=SUMPRODUCT(--(A1:A100="Yes")*(B1:C100))

just enter this with 'ENTER'

--
Regards
Frank Kabel
Frankfurt, Germany

"SteveMarine " schrieb im
Newsbeitrag ...
I need to perform a SUMPRODUCT on columns B and C based on the

results
of a lookup in column A. I thought an array formula like
{SUMPRODUCT(IF(a1:a100="yes",b1:b100,c1:c100))} would work, but no
luck. Anyone know what I'm doing wrong or a better way to approach it

?
Thanks


---
Message posted from http://www.ExcelForum.com/


  #7  
Old April 8th, 2004, 01:38 PM
Bernard V Liengme
external usenet poster
 
Posts: n/a
Default Array Formula

Hi Steve,
Try this =SUMPRODUCT(--(A1:A100="yes"),B1:B100,C1:C100)
Note this is NOT an array formula
The double negation coerces the Boolean values (FALSE & TRUE) to numeric (0
&1)
Best wishes
Bernard

"SteveMarine " wrote in message
...
I need to perform a SUMPRODUCT on columns B and C based on the results
of a lookup in column A. I thought an array formula like
{SUMPRODUCT(IF(a1:a100="yes",b1:b100,c1:c100))} would work, but no
luck. Anyone know what I'm doing wrong or a better way to approach it ?
Thanks


---
Message posted from http://www.ExcelForum.com/



  #8  
Old April 8th, 2004, 04:06 PM
Bob Umlas, Excel MVP
external usenet poster
 
Posts: n/a
Default Array Formula

The "--" is not necessary in this case. Use:
=SUMPRODUCT((A1:A100="yes")*B1:C100)

Bob Umlas

----- Bernard V Liengme wrote: -----

Hi Steve,
Try this =SUMPRODUCT(--(A1:A100="yes"),B1:B100,C1:C100)
Note this is NOT an array formula
The double negation coerces the Boolean values (FALSE & TRUE) to numeric (0
&1)
Best wishes
Bernard

"SteveMarine " wrote in message
...
I need to perform a SUMPRODUCT on columns B and C based on the results
of a lookup in column A. I thought an array formula like
{SUMPRODUCT(IF(a1:a100="yes",b1:b100,c1:c100))} would work, but no
luck. Anyone know what I'm doing wrong or a better way to approach it ?
Thanks
---

Message posted from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.