Reaching the SUMIF
Q. Our company provides financial reporting audits and IT audits. The client invoices provide an itemized list of time spent on each type of audit, by auditor and date. Is there an easy way to provide a total dollar amount related to each type of audit for the month?
A. The SUMIF function provides a quick and simple way to create a sum based on a specific criterion, such as the type of audit. Because you only have one criterion (type of audit), SUMIF is sufficient. If you had more than one criterion, you would need to use SUMIFS.
To use SUMIF, place your cursor where you would like the sum calculated for a particular type of audit. In the invoice in the screenshot below, I have placed my cursor in cell C46 to calculate a sum of all amounts related to the financial reporting audit.
The syntax for SUMIF is =SUMIF (range, criteria, [sum_range]). The first argument defines the range of cells you want evaluated for the criteria, which is the second argument and denotes the condition of the cells to include in the sum. The sum_range is the third argument and identifies the actual cells to be summed.
The screenshot below shows a situation similar to the one described in the question. Rows 6 through 43 list auditors’ names, hourly pay rate, hours worked, the total amount that should be billed, and whether the work was on a financial reporting (FR) audit or an information technology (IT) audit. To conserve space, the screenshot displays only rows 30—43, but you can see all the rows if you download the accompanying Excel file.
As you can see in the Excel file, the spreadsheet includes two SUMIF formulas to calculate total spend for financial reporting audits and IT audits, respectively. To determine how much was spent (and, thus, should be billed) on financial reporting audits, we need to establish the range as F6:F43 because it consists of the cells that contain FR or IT. The criteria in this case is “FR,” which is what we want Excel to find in the range F6:F43. The sum_range is E6:E43 because it contains the amounts to be summed. Put together, the formula is =SUMIF(F6:F43,”FR”,E6:E43), which calculates to $10,023.50, which is the total amount associated with financial reporting audits. Do the same in cell C47 to calculate the total amount for the IT audit. The only difference in the formula will be the criteria, which will now be “IT” instead of “FR.”
To see this approach played out using the Insert Function button, see the video below.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an assistant professor of accounting at Middle Tennessee State University.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
