How to use SUMIF in Excel?

We explain the SUMIF function in Excel by sharing several examples and showing you how its formula works for different types of criteria.

You may use SUMIF instead of VLOOKUP in some cases. If you are not familiar with the SUMIF function in Excel, be confident you will be after reading our tutorial!

The SUMIF function sums the values in a specific range that meet criteria that you specify.

For example, if you want to know how much you earned from customers who spent over $ 10k, you could use the formula =SUMIF(A2:A30,”>10000″) if amounts are in cells A2 to A30.

The SUMIF formula is =SUMIF(range, criteria, [sum_range])

SUMIF contains 2 required arguments and 1 optional one ([sum_range]).

range — the range of cells you want to evaluate.
criteria — a criteria defining which cells (within range or sum_range) to add to the sum.
sum_range — this argument is optional but often used. If you want to sum cells in a different range than the one you specified in range, you can do it by specifying where in sum_range. Check our various examples below for more information.

The SUMIF function will result with a number, a sum.

– Enclose text criteria or criteria with logical/mathematical symbols in quotation marks (“text“).
– You may use wildcard characters in the criteria argument. A question mark (?) will search for any single character and an asterisk (*) will match any sequence of characters.
Sum_range and range should be the same dimensions and shape or your Excel table may be impacted.

Learn how to use SUMIF by applying the formula =SUMIF(range, criteria, [sum_range]) and by checking examples below.

In this example, we use the SUMIF function to know how much European customers are spending with us.

The formula we use is =SUMIF(B9:B12,”Europe”,C9:C12).

In this example, we use SUMIF to know how much customers from South America and North America are spending with us. Olivia is from North America and Michelle lives in South America. Our criteria is “who lives in America” meaning South or North. We use the wild character * to search for any continents ending with “America”.

The formula we use is our Excel table is =SUMIF(B9:B12,”*America”,C9:C12).

Now, we want to know the sum represented by invoices with an amount inferior to $ 8,000.

The formula we use is =SUMIF(C9:C12,”<8000”). There are 2 invoices with an amount below $ 8,000: 4,500+5,000=9,500.

In this example, we want to know how much European customers are spending with us but we are using the cell D14, containing the text “Europe”, as criteria.

The formula we use is =SUMIF(B9:B12,D14,C9:C12).

Now, you know what the SUMIF formula is and how to use the SUMIF function in Excel. For some cases it might be better to use VLOOKUP; check this article to know what’s the VLOOKUP formula in Excel.

If you want to use SUMIF with multiple criteria, you’ll have to use SUMIFS instead. We’ll explain this slightly different function in a different tutorial. Stay tuned!

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More
Cope Paste Options on excel with shortcut keys

Copy paste values like a pro! (Excel+Google Sheets)

Copy and paste values (not formulas) using shortcut keys in Excel or Google Sheets! Learn how spreadsheets professionals do it within seconds and become one of them. Save time now...

Read More