This course will become read-only in the near future. Tell us at community.p2pu.org if that is a problem.

# Calculate the Company's Monthly Profits

Learn the Conditional Sum (SUMIF) Formula

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!

• ### Patrick said:

Hi, i've run into a problem with this last task. The problem is the date format. In the sales tab it is written as "jan, 2011" and in the profit tab it is written as "01/2011". Somehow this values are not considered as identical and the conditional sum returns zero. If I write the sales tab month in the same way as in the profit tab the formula works perfectly.

I have tried changing the column format to the same type of date format but couldn't get it to work. Maybe this is a problem that occurs in non-us/uk countries?

Greetings,

Patrick

### Nick Violi said:

Hey Patrick,

Yeah, date formatting is a total beast, so I was trying to skirt the issue as much as possible. Actually, technically, both columns are formatted as text and not dates, i.e. the spreadsheet recognizes them not as dates but as strings of letters and numbers (which of course don't match), so that's (part of) where the problem came from.

I fixed it in the original spreadsheet; thanks for the tip!

Nick

on June 15, 2012, 10:34 a.m. in reply to Patrick