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
Attachment:
example.gnumeric
Description: application/gnumeric