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

---

17 ways to OPTIMIZE VBA Code for FASTER Macros

BONUS VBA MACRO: Excel 2003 Style menu in Excel 2007 / 2010

Are you going for an interview ?
3 things to remember before Excel VBA Interview
50 Excel VBA Interview questions

or looking for a job ?
 Excel, Access, SQL, VBA, MIS, Reporting and Data Analysts Jobs

Shrink or Reduce the size of Excel Workbook without losing data

Learn: VLOOKUP         OFFSET        Custom Format        Array Formula