We're almost done. The last step is to calculate the total revenue and total cost for each month in the year. Head over to the Profit sheet.
You've already seen one way of filling in this table: you could write a SUM formula for each cell here, referring back to the sales sheet in each case to find the rows that refer to that month, but there's an easier way. We'll be using the conditional sum function, SUMIF. SUMIF takes three arguments: a range of cells containing the conditions, the criterion for matching against, and a range of cells containing the values to be conditionally summed. The function works by checking if each cell in the condition range (argument 1) exactly matches the criterion (argument 2), and if so, it includes the corresponding cell in the sum range (argument 3) into the running total. Note that the cell ranges in the first and third arguments must be of the same size.
Enter the formula
=sumif(Sales!$C$6:$C$32,B8,Sales!$G$6:$G$32)
into cell C8. In this function, you're asking the spreadsheet to check every value in column C of the Sales sheet to see if it matches the value in cell B8 (the month), and if it does, to add the revenue for that sale from column G to a running total. Once all the cells in column C have been checked, the running total is returned. Copy this formula into the revenue cells for the rest of the months of the year.
The monthly cost function is analogous. See if you can write the formula for that in column D.
Finally, calculate the monthly profit in column E by dividing the revenue for each month by the corresponding cost figure.
With all that done, the last thing we might want to do is to calculate the total cost, revenue, and profit for the entire year. Fill in cells C21, D21, and E21. One easy way to check for some types of errors is to make sure these values match the corresponding ones calculated in the Sales sheet.
Does everything match? Give yourself a pat on the back: you're on your way to becoming a great data wrangler!
