On Sat, 2010-02-27 at 00:44 +0100, Michael Goetze wrote: > Hi folks, > > I'd like to sum the elements of one column for which another column in > the corresponding row meets some criterium such as not being blank. So > something like > > =SUMIF(A1:A10,NOT(ISBLANK($_)),B1:B10) > > except that Gnumeric, unlike Perl, does not provide me with a $_ context > variable. > > In fact, the only thing I've figured out to get SUMIF and COUNTIF to > test for is equality with a constant. If that's the only possible > "criteria", I think this should be made explicit in the function > reference. If more is possible, I would appreciate some examples. > > Morten mentioned on IRC that this may be solvable with "array formulae" > and "implicit iteration". > > Thanks in advance, > Michael > > PS: Please CC me on replies, as I am not subscribed to the list.
=sum(if(isblank(B1:B9),0,A1:A9)) entered as an array formula (ctrl-shift-enter) will add the valiues in A1:A9 in which the corresponding values in B1:B9 are not empty. Note that DSUM is much more flexible than SUMIF but in this case a regular SUM combined with IF works the best I think. Andreas _______________________________________________ gnumeric-list mailing list [email protected] http://mail.gnome.org/mailman/listinfo/gnumeric-list
