A Case Study From The Sales Performance Analytics with Excel Course

“Your boss sends you an Excel file, containing the sales transactions of several products sold in the last quarter. In the file, column A shows the names of products sold by your company. In column B, it shows the customers who bought the product in that quarter. In column C, it lists down the week the customer bought a particular product. ”

Using the data given, you are required to tabulate the sales for each week by product as shown:

For someone who knows the basic of Excel, the only to get this done is to eyeball all the records and manually sum up the figures and enter them into the table.

If there is only one product, then a simple sumif formula which we teach in part I of our course would solve the problems in a few seconds. But with a listing of more than 10 products, this would take you much longer, considering that sorting is required and the numbers to sum up have to be are accurately identified without human error. But what if the listing has more than 50 products and span over a number of weeks? Just think about the time you would have to spend on tabulating the numbers.

But if you just know of an extra formula to join cells together, absolute and relative referencing, plus the sumif formula you already know, solving such a problem would probably take only slight over a minute. All we have to do is to have a new column to join the product code and week number together. Then by using sumif and the concatenate formula, we would be able to set up a formula that can be used for the entire table. This formula requires no eyeballing on the spreadsheet and yet produces the numbers or results with 100% accuracy.

If you want to save time by doing your Excel tasks right, sign up for our Sales Performance Analytics with Excel course now!