Re: Other Critera than Equality for SUMIF, COUNTIF, ...

[Andreas Guelzow <aguelzow pyrshep ca>]

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