The SUMIF vs. SUMIFS

The SUMIF vs. SUMIFS

Learn about one of the most widely used functions in Excel

The SUMIF / SUMIFS functions in excel are something I use pretty much anytime I open an excel file…

as it allows me to perform one of the most common math functions (SUM)…

based off of the results of one of the most common conditional functions (IF).

Let’s do a deep dive on how it works

➡️ How does the SUMIFS function work?

The SUMIF function allows you to analyze a single range of data 👀

and then SUM the range only if it meets a single criteria.

➡️ Syntax

=SUMIF(RANGE,CRITERIA,[SUM_RANGE])

Let’s break down this syntax:

◾ RANGE → The range of cells that you want to apply the criteria to.

◾ CRITERIA → This is the condition that defines which cells to add.

◾ SUM_RANGE → This is optional. The actual cells to sum if you want to sum a different range than the specified 'range'.

➡️ What are some important things to note about the SUMIFS function?

1️⃣ Ideal for single criteria.

2️⃣ Suitable for basic datasets.

3️⃣ Limited flexibility with a single condition.

4️⃣ Simpler syntax, easier to use.

➡️ How does the SUMIFS function work?

The SUMIFS function allows you to analyze a multiple range of data 👀

and then SUM the range only if it meets specific single / multiple criteria.

➡️ Syntax

=SUMIFS(SUM_RANGE,CRITERIA_RANGE1,CRITERIA1,[CRITERIA_RANGE2,CRITERIA2])

Let’s break down this syntax:

◾ SUM_RANGE → This is the ultimate range that you want summed when you are finished evaluating your equation

◾ CRITERIA_RANGE1 → This is the range that you want to evaluate for true / false

◾ CRITERIA1 → This is the criteria that you want to set for the Criteria Range 1 (ex: greater than 100, equal to a value)

The items in brackets for Criteria Range 2 are optional if you want to set multiple condition.

➡️ What are some important things to note about the SUMIFS function?

1️⃣ The SUMIFS function can do everything the SUMIF function can do and more

The SUMIF function is similar to the SUMIFS function, but it only allows you to set 1 criteria.

For that reason, I always use SUMIFS, as the syntax differs between the 2

2️⃣ When setting conditions, be sure to use the right syntax

Use this syntax when setting the conditions for greater / less, equal to, or not equal to:

◾ Greater than → “>”&A1

◾ Less than → “<”&A1

◾ Greater than or equal to → “>=”&A1

◾ Less than or equal to → “<=”&A1

◾ Equal to → A1

◾ Not equal to → “<>”&A1

3️⃣ SUMIFS is good for only one direction

Generally, you’ll want to use the SUMIFS function when evaluating a range either

VERTICALLY ↕️

or

HORIZONTALLY ↔️

===

There’s a lot more to say about these 2 functions…

but alas, I’ve reached my 3k.