Re: summing based on more than one criteria

[greigsteve <greigsteve gmail com>]

Hadnt realized how complicated this would be but have got some insight from
your explanation and formulae on the spreadsheet. I will do some more
homework before asking any more specific questions. Steve

Oliver Burnett-Hall wrote:
> On Sat, 25 Feb 2012 13:06:14 -0800 (PST)
> greigsteve <greigsteve gmail com> wrote:
> > I have created quite a big gnumeric spreadsheet for my business and
> > personal finance which so far has been great. I am now having a
> > problem summing a column based on criteria from two other columns. I
> > read a lot of stuff and did get this to work on a short gnumeric
> > spreadsheet using the sumproduct function. However I cant apply it to
> > what seems like a very analagous although longer and more complicated
> > situation in my database. It is not helped by the fact that one
> > criteria is a date.
>
> Take a look at the attached file.
>
> One thing I noticed was that you were using slightly different wording
> for the categories in different places, e.g. "Loose Tools" and "Loose
> Tools (VAT)".  It is possible to write formulas that can cope with
> this, but it's much easier if the text is identical.
>
> The other thing that I've changed is that I've changed the column
> headings for the months from the text "April" to the date value
> 2011-04-01 and formatted this using a customer number format so
> that you only see the month name.  This lets you use the column
> headings in the calculation.
>
> I've used array formulas instead of SUMPRODUCT() as I find them more
> flexible.  I hope that you can follow what I've done -- let us know if
> you need more explanation.  To make it clearer I've done it twice, the
> first sheet uses cell references (e.g. '$E$2:$E$5'), the second sheet
> uses named ranges (e.g. 'prices').
>
> HTH,
>
> - olly
>
>  
> _______________________________________________
> gnumeric-list mailing list
> gnumeric-list gnome org
> http://mail.gnome.org/mailman/listinfo/gnumeric-list

-- 
View this message in context: 
http://old.nabble.com/summing-based-on-more-than-one-criteria-tp33392230p33397037.html
Sent from the GnuMeric mailing list archive at Nabble.com.