# How to find sales by product

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. 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. 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. 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.  