Excel should automatically detect that you are entering dates, and will format the data accordingly. If the data is being auto-formatted as something else, highlight the cells and click the drop-down menu in the 'Numbers' section of the Home tab. Select 'Short Date' from the list of options. Answer: You should be able to create this formula without using the IF function or AND function. In Excel 2007 and higher, you could do this with the following SUMIFS formula: =SUMIFS (D1:D10,A1:A10,'=jim',B1:B10,'=ben',C1:C10,'=alice') This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly. Our free retirement and savings spreadsheets are designed for Microsoft Excel, so you can work with them on your own computer. One unique thing about our 401k and Savings calculators featured below is the ability to select a 'random rate' option, which assigns a random interest rate to each year so that you can experiment with.

We are too caught up in wanting stuff now, rather than saving up and buying stuff when we can afford it. These habits naturally lead to way too much debt, but with enough discipline and help, we believe anyone can get out of debt.

Our free debt reduction calculator, featured below, was designed to help individuals and the professionals that advise them to quickly and easily come up with a plan for eliminating debt. It may not be as easy to stick with the plan, but at least we can help you get started.

Featured Debt Payoff Calculators

Debt Reduction Calculator ▶

How fast can I get out of debt using the snowball technique? This is possibly the most valuable spreadsheet on this entire website!

Credit Repair Spreadsheet ▶

Pay down your debt to improve your FICO® credit score. This is an expanded, more advanced version of the Debt Reduction Calculator.

Credit Card Payoff Calculator ▶

Calculate the payment required to pay off a single credit card in X number of years.

Credit Card Minimum Payment Calculator ▶

Find out how much it really costs to make minimum payments.

Credit Card Balance Transfer Calculator ▶

Bose soundlink mini software update. Estimate how much you can save when using a credit card balance transfer to get a lower rate.

Savings Snowball Calculator ▶

Use this spreadsheet to estimate how long it will take you to reach your short-term and mid-term savings and debt payoff goals. The focus is on savings, but it is based on the debt reduction calculator, so it lets you include debt payoff in addition to your savings goals.

Mortgage Payoff Calculator with Line of Credit ▶

Use this calculator to evaluate the mortgage payoff acceleration strategy of using a line of credit to make extra payments with optional paycheck parking.

Debt Free Charts and Trackers ▶

Track your debt free journey using a column chart that gradually reveals a debt free image as you pay off your debt. Or download printable debt payoff trackers for your 3-ring binder.

Related Content

This Excel tutorial explains how to use the Excel SUMIFS function with syntax and examples.

Description

The Microsoft Excel SUMIFS function adds all numbers in a range of cells, based on a single or multiple criteria.

The SUMIFS function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUMIFS function can be entered as part of a formula in a cell of a worksheet.

Syntax

The syntax for the SUMIFS function in Microsoft Excel is:

Parameters or Arguments

sum_range

The cells to sum.

criteria_range1

The range of cells that you want to apply criteria1 against.

criteria1

It is used to determine which cells to add. criteria1 is applied against criteria_range1.

criteria_range2, .. criteria_range_n

Optional. It is the range of cells that you want to apply criteria2, .. criteria_n against. There can be up to 127 ranges.

criteria2, .. criteria_n

Optional. It is used to determine which cells to add. criteria2 is applied against criteria_range2, criteria3 is applied against criteria_range3, and so on. There can be up to 127 criteria.

Returns

The SUMIFS function returns a numeric value.

Applies To

• Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007

Example (as Worksheet Function)

Let's look at some Excel SUMIFS function examples and explore how to use the SUMIFS function as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following SUMIFS examples would return:

Using Named Ranges

You can also use a named range in the SUMIFS function. A named range is a descriptive name for a collection of cells or range in a worksheet. If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range.

For example, we've created a named range called cost that refers to column C in Sheet 1.

Then we've entered the following data in Excel:

Based on the Excel spreadsheet above, the following SUMIFS examples would return:

To view named ranges: Select the Formulas tab in the toolbar at the top of the screen. Then in the Defined Names group, click on the Defined Names drop-down and select Name Manager.

The Name Manager window should now appear.

Frequently Asked Questions

Question:In Microsoft Excel 2003, I'm trying to work out a formula for the following:

If any row in column a = 'jim' AND any row in column b = 'ben' AND any row in column c = 'alice' then add together column d for all the rows that match the criteria. I can't seem to get my ANDs and IFs formula right!

Ayadi Calculations Excel Spreadsheet

Answer:You should be able to create this formula without using the IF function or AND function. In Excel 2007 and higher, you could do this with the following SUMIFS formula:

This will evaluate the first 10 rows of data in your spreadsheet. You may need to adjust the ranges accordingly.

But since you are using Excel 2003 and the SUMIFS function has yet been implemented by Microsoft, you will need to use an array formula. So instead you can create the following array formula:

Ayadi Calculations Excel Template

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

Ayadi Calculations Excel Format

This will perform the exact same calculations as the SUMIFS function above, but will work for versions of Excel 2003 or earlier.