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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|