Faced with a long sales ledger with a month’s worth of sales, it can be daunting to find a way to compile the data and get some meaningful feedback. In this month’s spreadsheet tips we look at the SUMIF function which is used to add things together depending on the criteria we present.
In our flower shop, Blooming Data, the manager is looking over the sales ledger. He wants to see how many roses he’s sold over the period so that he can have a good idea of what to re-order from the supplier. He is also interested in finding out the total sales for roses and how much he sells them for on average as he has different price levels by quantity, see Figure 1.
Spreadsheet Data 1
In Figure 1, we see the entries in the sales ledger for February. There is also some price break information for roses which explains the different unit price rates in the ledger. How many roses have we sold in total, for how much? What was the average price? And between which two dates?
First, we start by determining the date range we will be looking at. To do this we use the min() and max() functions to pull out the oldest and newest dates from the sales ledger. The dates are in column B, and more precisely in cells B13 to B19, written B13:B19. In cell H12 we write the equation; =min(B13:B19)
and in cell H13, we write =max(B13:B19)
. See Figure 2.
Spreadsheet Data 2.
Now we know the date range of the entries in the sales ledger. What was the total number of roses sold between the two dates? We could go through the ledger one line at a time, check if it’s for roses, make a note of the quantity on a piece of paper then add it all together at the end. Or, we could save a bunch of time and use the SUMIF function.
The SUMIF function has three elements. Where we are searching, what we are searching for and the value we want. In this instance, we are searching in cells A13:A19, for ‘Rose’, and we want the values in C13:C19 (the quantity sold). The formula takes this form, =SUMIF(A13:A19,”Rose”,C13:C19)
This tells Excel to search through cells A13 to A19 for the word ‘Rose’. It takes the relevant values from column C and adds them together to give you a single figure for all the rose sales in the ledger. See Spreadsheet Data 3.
Spreadsheet Data 3.
We can also find the total sales value for all the rose sales using the SUMIF function. In cell H16 we write =SUMIF(A13:A19, “Rose”, E13:E19)
. It is almost the same as the previous formula, except this time we want the formula to add up the values in column E, the Total sales price for each transaction. See figure 4.
Spreadsheet Data 4.
Finally, it would probably be interesting to know what the average sales price for roses is. For this we divide the total value of all the sales, cell H16, by the total number of roses sold, H15. We write in cell H17, =H16/H15
See Spreadsheet Data 5.
Spreadsheet Data 5.
With the SUMIF function we can get totals and results from long lists of information, such as a sales ledger, in seconds. If you have any questions about SUMIF functions and when to use them, or you have a topic you wish us to cover in a future newsletter, please contact us.
