BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Difference between SUMIF and SUMIFS Function: Excel Formulas & Functions


1. SUMIFS is available only in MS Excel 2007 while SUMIF is available in both .i.e Excel 2003 and Excel 2007.

2. SUMIF is used to add a single continuous range based on single specified range with a single criteria but SUMIFS can be applied over multiple continuous range of same size and shape on multiple specified range equal to number of criteria applied.


SUMIF (Excel 2003)
Syntax - SUMIF(Criteria Range,Criteria,Sum Range)

1. One could use SUMIF() to sum all the values in a column that are above a particular value :
=SUMIF(B1:B23,">1000")
Illustration - If sum range is left blank, Criteria range becomes the sum range.

2. One could use SUMIF() to sum all the values in a column that are above/below a particular value of another column:
=SUMIF(A1:A23,">1000",B1:B23)
Illustration: This will sum all the values of Range B1 to B23, where it founds the value in the Range A1 to A23 greater than 1000.

3. If column A contains a list of dates, you could use SUMIF() to sum all the amounts in column B that are on or after a particular date in column A:
=SUMIF(A1:A23,">=26/05/84",B1:B23)

SUMIF function was designed to work with only one criteria so, if there is a need to total all the amounts between two dates, a single SUMIF() formula would not suffice and you would have to resort to using multiple SUMIF() formulae or an array formula.

Which in turn has allowed us to move to Excel 2007 and the new SUMIFS() function.


SUMIFS (Excel 2007)
Syntax - SUMIF(Sum Range,Criteria Range1,Criteria1,Criteria Range2,Criteria2 ....)

1. Total all the amounts between two dates :-
SUMIFS(B1:B23,A1:A23,">=26/05/84",A1:A23,"<=26/04/85") Illustration: Our two range/criteria pairs are: A1:A23,">=26/05/84" – include all amounts in B1:B23 where the corresponding date in A1:A23 is greater than or equal to 26 May 1984;
A1:A23,"<=26/04/85" – include all amounts in B1:B23 where the corresponding date in A1:A23 is less than or equal to 26 April 1985

BL said...

What if you want to replace the date with a cell? It doesn't work to do this in excel 2007: "<="C12 Where c:12 is the date.

What would you din instead

Anonymous said...

"<="&C12

Anonymous said...

@BL
"<="&C12

Alicia said...

Question, I am trying to use Sumifs to calculate the amt paid for each type of camp and within each distinct deposit so I have two criteria...i set the formula up and it works great until I change the sum range and the criteria range to 1500 (it stops working at 1000) is there a limit to the size the criteria range can be?

If so is there a way around that?

Alicia said...

Hello, I am using sumifs to calculate the initial deposit for camps based on camp type and deposit number so I have two criteria and it works great until I increase the criteria range and the subrange to 1500...it works for 1000 but nothing larger.

Is there a 1000 limit on the criteria range? and if so is there a way around this?