Excel 2007 SUMIFS(): The accountant's favourite Excel function gets an overhaul. By Simon Hurst

Posted by AccountingWEB PM | on Mon, 25/06/2007 - 18:13 54351 4 comments

SUMIF() is a very useful function for working with accounting data. As its name implies, it sums a range of cells if their contents, or the contents of an equivalent range of codes in another column, match a particular criteria.

You could use SUMIF() to sum all the values in a column that are above a particular value (in the examples, we have included the criteria data directly in the formula for simplicity – it would be much better design to include the criteria data in a separate cell and refer to the cell from the formula):

=SUMIF(B1:B10,">1000")

Continued...

A grammatical observation

JayDeeKay | Tue, 16/12/2008 - 19:47 | Permalink

Good article!
Small correction: the word "criteria" is the plural of "criterion". Therefore, "a criteria" is jarringly incorrect.

Ain't English interesting ...?

JDK

SUMIFS does not work on Excel 2003 and previous versions

jfranco | Thu, 23/09/2010 - 16:24 | Permalink

Hi,

Excellent review of the new SUMIFS function!

I just wanted to make a point for those who start to use it but his clients and colleagues keep using Excel 2003

If this is your case, you will need to create a SUMIFS tweak so their Excels make sense of it:

To turn this formula: =COUNTIFS('Sales'!$A$4:$A$400,"JAN",'Sales'!$V$4:$V$400,1)

You could write something like this formula: =SUMPRODUCT(('Sales'!$A$4:$A$400="JAN")*('Sales'!$V$4:$V$400=1))

These links may help:

http://www.excelforum.com/excel-new-users/745944-countifs-formula-conver...

http://www.excelsumproduct.com/

SUMIFs for multiple criteria where and AND would be used

RichardWhight | Mon, 08/11/2010 - 14:02 | Permalink

I want to do something special. I want to have all my criteria sitting outside the formula so that you can adjust it by amending cell values. This sounded simple until I got to this stage. For your information I'm using a really old version of SUN with ISAM files so database queries are not an option.

Datatable Range = A1:S10562

Account Code Range = A1:A10562

Analysis Code Range = F1:F10562

Value to Sum Range = E1:E10562

I need a formula that will sum up based on the Criteria

Analysis Code Range must have the value "6055"

Account Code Range must have Account Codes 1100 to 1104 OR 1300 OR 3000 and miss the inbetween Account Codes. I'd also like this as one criteria so that I can reference a single cell for the criteria and use the formula for all lines but the criteria may be different on each line.

Does this make sense?

Thanks

Comparison operator separate from value

shurst | Tue, 01/02/2011 - 16:15 | Permalink

If I understand correctly, I think you need to put the criteria values in cells, and then either put the comparison operators into other cells or hard code them into the formula - concatenating the comparison operators (or cells containing the comparison operators) with references to the criteria cells. Something like:

=SUMIFS($B$2:$B$11,$B$2:$B$11,">=" & $H$2,$B$2:$B$11,"<=" & $H$3) or:

=SUMIFS($B$2:$B$11,$B$2:$B$11,$G$2 & $H$2,$B$2:$B$11,$G$3 & $H$3)

The criteria cell references could be made relative if you wanted to use different values in different rows.