Using SUMIF() and IF() functions together to conditionally add different numbers

SUMIF() is great for adding different groups of numbers. See http://openoffice.blogs.com/openoffice/2009/08/using-sumif-in-openoffice-calc-spreadsheets-.html for more info on that function.

But let's say you want to add up one set of numbers in one case, and another if something else is true.

You can use IF to put together two SUMIFs. Here's a basic example.

=IF(K2="PHP";SUMIF(G2:H9;G2;H2:H9);SUMIF(G2:H9;G5;H2:H9))

Let's say you've got two developers at your web site development company. One of them specializes in PHP; the other in JavaScript and Wordpress. You want to list the revenue for projects you've done, but you want to list only the ones for PHP, if you're putting together a bid for a PHP project; otherwise you'll list the revenue for JavaScript and Wordpress.

You're putting together a template. Here's the example.

So you put together a function, IF K2 is PHP, then sum up the revenue for Bob; otherwise sum up the revenue for James.

 

Here's one with lines from the formula.

So you get this figure if it's PHP

 

And this if it's anything else.

---

---

Comments

[Forex Advice]

It's interesting to note that OpenOffice offers better mathematical possibilities than MS Office.