Alternative formula to SUMIF - Needs more than 1 Criteria

mazarin · General User Joined: 16 Feb 2009 Posts: 25

Hello Everyone!

Let´s consider this table:

Project Code | Category | Cost
-------------------------------------
AX | 1 | 10
AX | 1 | 10
AX | 1 | 10
AX | 2 | 10
AX | 2 | 10
ZM | 1 | 10
ZM | 1 | 10
ZM | 2 | 10
ZM | 2 | 10
------------------------------------

I would like to know the total cost for project "AX" on category "1". The expected result is: "30".

Since SUMIF allows only 1 criteria, how can I get the result? I´ve tried to obtain this using array formulas and SUMPRODUCT, but I

couldn´t make it work.

Thanks!

JohnV · Posted: Wed Apr 22, 2009 7:51 am Post subject:

Take a look at the DSUM function in Help. Note that there is a sample spreadsheet at the top of the help page that is used in the example's syntax.

mazarin · General User Joined: 16 Feb 2009 Posts: 25

Hello JohnV!
It seems that DSUM can sucessfully bring the result I want, but doesn´t fit my needs because:

1. I have dozens of criteria combinations (25+ projects and 30 categories)
2. DSUM needs a label / header identical to the database to show the results. This breaks the flexibility of the spreadsheet layout.

There is another way to bring the results?

Villeroy · Posted: Wed Apr 22, 2009 10:09 am Post subject:

mazarin · General User Joined: 16 Feb 2009 Posts: 25

Could you please post a sample of the formula, Villeroy?

I´ve got an error on my question, sorry! Let me remake it:

I would like to know the total cost for project "AX" on category "1". The expected result is 30.

keme · Posted: Wed Apr 22, 2009 11:40 pm Post subject:

Assuming your data range is A2:C10, project code is text, and that the other columns are not input as text:
=SUMPRODUCT(A2:A10="AX";B2:B10=1;C2:C10)

mazarin · General User Joined: 16 Feb 2009 Posts: 25

It worked! Thanks Keme!

So I think that SUMPRODUCT it is the better solution when we need to sum a data range based on two or more criteria. It´s the perfect alternative to SUMIF.

Thanks again, and hope this thread helps other people too!

keme · Posted: Thu Apr 23, 2009 5:34 am Post subject:

You're welcome.

SUMPRODUCT() is a surprising solution to this, perhaps. Anyway, since Villeroy enlightened me about its various uses, I've been much less strained when working in Calc.