## Using MAX IF With Multiple Criteria

Excel doesn’t have a MAXIF function, but we’re able to create our own version, by combining the MAX and IF functions. But how about a bigger dream? Could we create our own MAXIFS function too, with multiple IFs?

Using MAX and IF

Last week, we used MAX and IF in an array-entered formula, to find the latest date that a product price was changed. Remember to press Ctrl + Shift + Enter after typing this formula, instead of just pressing Enter.

=MAX(IF(\$A\$2:\$A\$9=A12,\$B\$2:\$B\$9))

Note: For more information on array formulas, I recommend Mike Girvin’s book, Ctrl+Shift+Enter: Mastering Excel Array Formulas.

Continue reading Using MAX IF With Multiple Criteria

## Get Latest Price for Specific Product

In a previous article, we combined the MAX and IF functions, to find the highest price for a specific product.

Today we’ll use that technique to find the latest date that a product’s price was changed. Then, using the product name and that date, we can find the latest price for the product – even if it isn’t the highest price.

Product Pricing Table

In this example, there is a price list in cells A1:C9. There are three columns: Product, Date and Price.

Find the Latest Date

If we want to find the latest price for Pens,

Continue reading Get Latest Price for Specific Product

## Count Items in a Date Range in Excel

If you have a list on your worksheet, and a date in each row, you can get Excel to count how many dates are in a specific date range. For example, how many Pencil shipments went out in September, based on the Ship Date column?

Use COUNTIFS and Cell References

The easiest way to find the number of shipments is to use the COUNTIFS function (Excel 2007 and later) and enter the product name, and the start and end dates on the worksheet.

In this example, there is a drop down list of products in cell A2. The

Continue reading Count Items in a Date Range in Excel

## Check Winning Numbers with COUNTIF Function

Today, we’ll see a little magic from the COUNTIF function – it can do the work of seven formulas in one!

The COUNTIF function can check a range of cells, and see if it contains a specific value. For example, type your lucky number in cell D2, and use a COUNTIF formula to see if that number is found in the list of winning numbers in cells I2:N2.

=COUNTIF(I2:N2,D2)

The number, 26, is found once in the range I2:N2, so the COUNTIF result is 1. Your lucky number is a winner!

Check Multiple Numbers

What if we have multiple

Continue reading Check Winning Numbers with COUNTIF Function

## Change Functions with AGGREGATE in Excel

A couple of years ago, we looked at the Excel SUBTOTAL function, and saw how you could allow users to select the function they want. In the example shown below, the total formulas are controlled by the drop down list at the top of the sheet.

In Excel 2010 the AGGREGATE function was introduced, and it’s like a supercharged version of SUBTOTAL. There is a longer list of functions, plus options on what data, if any, to ignore.

For some of the functions, you can use the Reference form, with these arguments:

AGGREGATE(function_num, options, ref1, [ref2], …)

For a

Continue reading Change Functions with AGGREGATE in Excel

## Switch Languages in Excel Workbook

Our two official languages in Canada are English and French, and I’m currently working on an Excel project in which you can choose either language.

The data in my client’s file is confidential, so I’ll show you how this works using some text from a Honey Nut Cheerios box. Actually, this is how many of us learned some French – reading the back of the cereal box at the breakfast table!

Set up the Translation Table

In the sample Excel file, I set up a table with 3 columns – ID, English and French. I entered a few English and

Continue reading Switch Languages in Excel Workbook

## Array Formulas Book Giveaway

It’s August already! How did that happen? Did you know that August is not only a month name, it’s an adjective that means “inspiring reverence and admiration; venerable, impressive”.

One way to build admirable Excel skills is to attend Chandoo’s online PowerPivot course, and enrolment opens today. In addition to the basic course, there is a new advanced level course, taught by Rob Collie, who used to work on Microsoft’s PowerPivot team.

In Excel, it’s also impressive to know how to use array formulas. If you’d like to develop august array skills, here’s your chance to win an e-book

Continue reading Array Formulas Book Giveaway

## See Formula Results in an Excel Data Table

Do you ever use the Data Table feature in Excel? It’s one of the “What-if Analysis” tools, found on the Ribbon’s Data tab, along with Scenario Manager, and Goal Seek.

A data table lets you experiment with one or two variables in a formula, and see the results, in a compact layout.

Get Ready to Build a Data Table

Before you can build a data table, you need a formula on your worksheet. It should use cell references in its arguments, like this PMT function, in cell C7.

The variables are in cells C2, C3, and C4, and

Continue reading See Formula Results in an Excel Data Table

## Use Check Box Result in Excel Formula

To make it easy for people to enter data  on a worksheet, you can insert a check box control, using the Form Control tools on the Developer Tab. If you don’t see a Developer tab, there are instructions here for showing it.

Adding these controls to a worksheet can make it easy for people to enter data – they just click to select the option that they want.

But, after they’ve checked that box, how do you capture that information, and use it in your formulas?

Link the Check Boxes to Cells

Continue reading Use Check Box Result in Excel Formula

## Calculate Loan Payments with PMT Function

Even if you’re not a financial wizard, you’ve probably had to figure out a loan payment at least once in your life. Fortunately, Excel makes it easy, with the PMT function.

PMT(rate,nper,pv,[fv],[type])

If you have used this function, you probably just used the first three arguments – rate, nper, pv – and ignored the optional ones – fv and type.

In the example below, I entered the annual rate (rate), number of payments (nper), and loan amount (pv) on the worksheet, then referred to those cells in the PMT formula.

=PMT(C2/12,C3,C4)

What is the Future Value?

Do you

Continue reading Calculate Loan Payments with PMT Function