Re: summing based on more than one criteria



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



[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]