#Merge duplicates in excel for mac 2011 how to
#Merge duplicates in excel for mac 2011 code
#Merge duplicates in excel for mac 2011 download

#Merge duplicates in excel for mac 2011 download

If you won’t have time, or if your drawing skills are worse than mine, you can download the sample Excel Valentine file, at the end of this blog post.Īnd if you want some romantic music in the background, while you work on your Excel Valentine card, you can listen to the YouTube playlist, compiled by John Walkenbach and his blog readers. Your boss won’t mind if you spend a couple of hours working on this today, because it’s an Excel project! This Excel Valentine card uses a named range, data validation, a formula, and conditional formatting (to change the heart from white to pink to red). I use Ctrl+Shift+Enter since it’s an array formula.Yes, it’s Valentine’s Day today, and if you were too busy to buy your sweetie a card yesterday, you can make one in Excel. =AVERAGEIF(donations!A:A,”*”&B34&”*”,donations!D:D)īut how do I work out the minimum and maximum amounts given by each code? I’ve tried:īut it just gives me a zero value.

#Merge duplicates in excel for mac 2011 code

I can easily work out the total and average donation amounts for each of those criteria codes (where I’m looking up the criteria code in row 34): On the Donations sheet column A lists the UIDs and column D lists the donation given by each donor. Each donor is tagged with an alphanumeric UID ending in letters which correspond to various criteria – an A in their UID means they’re an Active Member, X for ex-trustees, P for prospect donors, V for volunteers a UID can have multiple letters if they meet more than one of those criteria.Ĭolumn B of my Sheet1 lists each of the unique criteria codes (P, V, X….). I have a file with a sheet (“Donations”) containing a list of donations. _ Author Debra Dalgleish Posted on JJCategories Excel Formulas The sample file for this video can be downloaded from my Contextures website, on the MIN and MAX page. To see the steps for creating MIN IF and MAX IF formulas, watch this short Excel video tutorial. If you’re ready for the next challenge, you can also calculate MIN IF and MAX IF with Multiple Criteria Watch the MIN IF or MAX IF Video These formulas have only one criterion - the product name. Or use absolute references for the product and quantity ranges: To find the maximum quantity for a specific product, use MAX instead of MIN. Click in the formula bar (it doesn’t matter where you click within the formula).If you don’t see those curly brackets, you pressed Enter, instead of Ctrl + Shift + Enter. Those were automatically added, because the formula was array-entered (Ctrl + Shift + Enter). If you select cell D11, and look at the formula in the Formula Bar, there are curly brackets at the start and end of the formula. NOTE: If you plan to copy this formula down a column, use absolute references to the product and quantity ranges: Then press Ctrl+Shift+Enter to array-enter the formula.If the product name matches, we want to check the product quantity Next, select D2:D8, where the quantities are listed.

Select C2:C8, where the product names are listed.

Next, we want to find the rows where the product name matches the product in C11: The formula starts with the MIN and IF functions, and their opening brackets:

NOTE: You can also calculate MIN IF and MAX IF with Multiple Criteria MIN IF Formula To make it easy to select a product, I created a drop down list of product names, by using data validation. I’ve selected a product in cell C11, and the formula will be built in cell D11. So, we’ll have to create our own MINIF formula, using MIN and IF. There is a SUMIF function and a COUNTIF function, but no MINIF or MAXIF. In this example, we want to see the MIN and MAX for a specific product.

#Merge duplicates in excel for mac 2011 how to

Now you’re ready for advanced calculations, like how to find MIN IF or MAX IF in Excel. When you were first learning how to use Excel, you quickly discovered the basic Excel functions, like SUM, COUNT, MIN, MAX, and AVERAGE.