How to find sales by supplier

There is no doubt that having up to date, accurate and relevant information on your customers is a very useful business tool. In this month's Spreadsheet Tips, we look at how we can quickly pull out some key figures from a sales list, using the CountIF function. Knowing where you are always helps you make decisions about what to do next, to get where you want to go.

In our perfume boutique, Scented D'ata, the manager is compiling the monthly sales information. It is also time to re-order, but how many, exactly, sold last month? With this figure, will he be able to secure a discount from his supplier? The sales list is shown in Spreadsheet Data 1.

Spreadsheet Data 1

In cell F5, we write the CountIF formula. CountIF requires two pieces of information; where to look and what it is looking for. Where to look is called the 'Range' and what to look for is the 'Criteria'. The Range is the cells in the spreadsheet that you want to look in, in our example it is cells E10 to E14, which we write E10:E14. You can click and select on the cells instead of typing in the cell addresses. Excel also brings up a little help box when you type in the CountIF function to remind you what information it requires. See Spreadsheet Data 2.

Spreadsheet Data 2

We now put dollar signs next to each part of the cell references. E10:E14 becomes $E$10:$E$14. This locks the position of the range we want to look up. If we now drag the formula down the column, the 'Range' will not alter. Next we type a comma. This tells the function that we have finished giving it the 'Range' and want to move on to the 'Criteria'. The criteria is the name of the supplier. We could type in the supplier's name here, or put in the cell reference of a cell which already contains the supplier's name, i.e. E5. See Spreadsheet Data 3.

Spreadsheet Data 3

This gives the answer 4. We can do a quick visual check on the sales list in our example and check that this is correct. In a real environment, you have just saved yourself an hour or more. See Spreadsheet Data 4.

Spreadsheet Data 4

Because we used the cell reference, E5, rather than typing in the supplier's name into the CountIF function, we can now drag cell F5 down to F6 and it will count the number of sales against products supplied by our other supplier, Feline Memories. The Range will remain the same because we used the dollar signs to lock it, and the criteria will move down one row, from E5 (Wagon Trail Perfumery) to E6 (Feline Memories). See Spreadsheet Data 5.

Spreadsheet Data 5

We drag down and the formula calculates one sale. You have just saved yourself another hour, at least. See Spreadsheet Data 6.

Spreadsheet Data 6

There are many time saving, business enhancing tools available in the PC nowadays. Working Data can help you unlock them. Call us on 01295 680 986 and we will talk it over with you.

..back to top