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 <greigst...@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. _______________________________________________ gnumeric-list mailing list gnumeric-list@gnome.org http://mail.gnome.org/mailman/listinfo/gnumeric-list
