Noel Lynne Figart

The Secret to SUMIF Today we’re going to learn about some features of Excel and how they interact. • • Tables and how they’re named SUMIF We’re going to do this with a little problem our hypothetical boss has given us. It’s the end of the first quarter. She would like to know how much each client has paid us for this quarter. We’re going to get all of this information from a little ledger she sent us. She’s no Excel expert, but she’s meticulous about records. Luckily, we have plenty to work with just from this little table. Date 1/2/2017 1/4/2017 1/9/2017 1/9/2017 1/11/2017 1/31/2017 2/1/2017 2/3/2017 2/3/2017 2/5/2017 2/8/2017 2/8/2017 2/10/2017 2/14/2017 2/28/2017 3/2/2017 3/5/2017 3/5/2017 3/6/2017 3/7/2017 3/8/2017 3/13/2017 3/15/2017 3/16/2017 3/30/2017 4/2/2017 Client Hogwarts Torchwood Bene Gesserit The Royal Frog Trampling Institute The Monitor's Guild Church of All Worlds Torchwood Council of Elrond The Royal Frog Trampling Institute Church of All Worlds Hogwarts The Monitor's Guild Bene Gesserit The First Order Xavier Institute for Gifted Youngsters Bene Gesserit Torchwood Torchwood Hogwarts The Royal Frog Trampling Institute Torchwood Bene Gesserit Hogwarts Hogwarts The Royal Frog Trampling Institute The Monitor's Guild Source Nepotism Advertising Competitive Bid Competitive Bid Competitive Bid Competitive Bid Advertising Advertising Competitive Bid Competitive Bid Nepotism Competitive Bid Competitive Bid Nepotism Nepotism Competitive Bid Advertising Advertising Nepotism Competitive Bid Advertising Competitive Bid Nepotism Nepotism Competitive Bid Competitive Bid Work Type Payment IT Consulting $ 1,729.00 Copywriting $ 2,020.00 Training $ 1,900.00 IT Consulting $ 3,829.00 Training $ 4,983.00 IT Consulting $ 4,422.00 Training $ 1,772.00 Training $ 1,029.00 Training $ 2,063.00 Copywriting $ 2,929.00 Copywriting $ 1,943.00 Training $ 3,872.00 IT Consulting $ 5,026.00 IT Consulting $ 2,032.00 Training $ 3,098.00 Training $ 4,465.00 Copywriting $ 1,072.00 Training $ 1,943.00 IT Consulting $ 2,063.00 IT Consulting $ 2,972.00 Copywriting $ 3,872.00 Training $ 5,026.00 IT Consulting $ 1,072.00 Training $ 2,075.00 IT Consulting $ 3,141.00 Copywriting $ 1,772.00 Notice that we have a simple ledger that records the date, the client, how we got the client, what work we did for the client, and how much we money we received. The Secret to SUMIF Figart Consulting Portfolio 1 The first thing we will do is turn this array into a table, which will make the rest of the calculations easier. Tables in Excel To turn an array into a table in Excel: 1. Select the range you would like to make a table. (For this exercise, that’s going to be cells A1:E27) 2. On the Home tab in the Styles group, click on Format as Table. 3. Choose the table style you would like. 4. Make sure that in the Format as Table dialog box, the My table has headers checkbox is checked. 5. Click OK. You have now formatted your array as a table. A table is more than pretty formatting, however. You have created a powerful object with some specific functions. The Secret to SUMIF Figart Consulting Portfolio 2 Date 1/2/2017 1/4/2017 1/9/2017 1/9/2017 1/11/2017 1/31/2017 2/1/2017 2/3/2017 2/3/2017 2/5/2017 2/8/2017 2/8/2017 2/10/2017 2/14/2017 2/28/2017 3/2/2017 3/5/2017 3/5/2017 3/6/2017 3/7/2017 3/8/2017 3/13/2017 3/15/2017 3/16/2017 3/30/2017 Client Hogwarts Torchwood Bene Gesserit The Royal Frog Trampling Institute The Monitor's Guild Church of All Worlds Torchwood Council of Elrond The Royal Frog Trampling Institute Church of All Worlds Hogwarts The Monitor's Guild Bene Gesserit The First Order Xavier Institute for Gifted Youngsters Bene Gesserit Torchwood Torchwood Hogwarts The Royal Frog Trampling Institute Torchwood Bene Gesserit Hogwarts Hogwarts The Royal Frog Trampling Institute Source Nepotism Advertising Competitive Bid Competitive Bid Competitive Bid Competitive Bid Advertising Advertising Competitive Bid Competitive Bid Nepotism Competitive Bid Competitive Bid Nepotism Nepotism Competitive Bid Advertising Advertising Nepotism Competitive Bid Advertising Competitive Bid Nepotism Nepotism Competitive Bid Work Type Payment IT Consulting $ 1,729.00 Copywriting $ 2,020.00 Training $ 1,900.00 IT Consulting $ 3,829.00 Training $ 4,983.00 IT Consulting $ 4,422.00 Training $ 1,772.00 Training $ 1,029.00 Training $ 2,063.00 Copywriting $ 2,929.00 Copywriting $ 1,943.00 Training $ 3,872.00 IT Consulting $ 5,026.00 IT Consulting $ 2,032.00 Training $ 3,098.00 Training $ 4,465.00 Copywriting $ 1,072.00 Training $ 1,943.00 IT Consulting $ 2,063.00 IT Consulting $ 2,972.00 Copywriting $ 3,872.00 Training $ 5,026.00 IT Consulting $ 1,072.00 Training $ 2,075.00 IT Consulting $ 3,141.00 Before we get to the problem our boss has asked us to solve, I want to do one more thing – name the table. If you are going to be using more than one table in a spreadsheet and will be performing calculations from more than one table, it is a good idea to give each table a meaningful name. While we are not going to go quite that far in this exercise, it’s a good general habit. To Name a Table in Excel: 1. Make sure a cell in the table is selected. 2. Look at the top of your screen at the different tabs for ribbons. The last one should be Design with a Table Tools as a label above it. Click on it. 3. On the Table Tools:Design ribbon in the Properties group, you will see a Table Name text box. 4. Type the name you want for your table and press on your keyboard. (We’re calling this table “Ledger”) Now that we have formatted the array as a table, we are going to look back at what our boss wants. The first thing she wants is how much each client has paid. We will start by creating another little array. Then we will use the SUMIF function to add up how much each client has paid us. As we go through The Secret to SUMIF Figart Consulting Portfolio 3 this, you will see why I prefer to do this as a table rather than just keeping the array as-is. For this exercise, we’re presuming that we’ll be starting an array to the side of the Ledger table starting in cell G1. The formula we will use in cell H2 will be: =SUMIF(Ledger[Client], G2, Ledger[Payment]) Let’s break this down, as it might look like gobbledygook at first. The function we will use is SUMIF, so we start with =SUMIF. The syntax for the Sumif function is: =SUMIF(range, criteria, sumrange) So, the range is the comparison. In this case, we want the function to look in the Client column of the Ledger table, which is written as Ledger[Client], with the table name and the column name in brackets right beside it. Then we want to know the criteria for comparison. There are a couple of ways to do this. You could look for specific text by putting it in quotes. So, we could have written it as “Bene Gesserit” (You put quotes around the text you’re searching for). Which begs the question, why did I use the cell reference G2 instead? Well, mostly because I am lazy. I want to be able to write this equation once, then use autofill to complete the rest of the array. The last thing we want to know is where to look for the values for which we want a total. In this case, it is in the Payment column of the Ledger table, so we use Ledger[Payment]. Client Bene Gesserit The Royal Frog Trampling Institute Torchwood The Monitor's Guild Hogwarts Church of All Worlds Xavier Institute for Gifted Youngsters The First Order Council of Elrond Total =SUMIF(Ledger[Client], G2, Ledger[Payment]) Pop quiz. If I did not set this up as a table but left the array as-is, could I have used cell references instead? My word, yes. In this particular example, =SUMIF(Ledger[Client], G2, Ledger[Payment]) would become: =SUMIF($B$2:$B$27, G2, $E$2:$E$27) The Secret to SUMIF Figart Consulting Portfolio 4 Notice that I made the cell references absolute? Again, lazy. I want to be able to write this formula once and copy it down. So, why would I not do it that way? Best practice is to try to give meaningful names to ranges of data one is manipulating so that it is easy to think about what data the user is trying to pull rather than being bogged down in making sure cell references match exactly. Tables are not the only way to do this. Stay tuned next week, where we’ll talk about Named Ranges and how to use them. Please feel free to contact me with any questions you may have! The Secret to SUMIF Figart Consulting Portfolio 5