Q. I read the June 2020 JofA article on SUMIF. I have tried to use it in my Excel spreadsheet, but it won't work since I have multiple criteria. Is there a way to get SUMIF to work if you have more than one condition?
A. SUMIF will only sum up totals based on one condition (also referred to as criteria). In that June 2020 JofA article (see "Reaching the SUMIF," JofA, June 2020), we summed up the total dollar amount for just the financial reporting audits and then summed up the total dollar amount for just the IT audits. Each sum had only one condition — the type of audit. If you need to create a sum based on more than one criteria, you can use SUMIFS. It accomplishes the same task as SUMIF but allows you to set anywhere from one to 255 conditions.
I have created a workbook with an example of using SUMIFS. You can access here and view the accompanying video at the end of this article. Note that the content that follows is based on Microsoft Excel 365 for PCs. Other versions of Excel may work differently.
The syntax for SUMIFS is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). The first three arguments are required, with additional arguments to be added depending on how many criteria are to be included. The first required argument is sum_range, which are the actual cells to sum. The second required argument is criteria_range1, which is the range of cells where the criteria are located. The third required argument is criteria1, which is the criteria to be found in criteria_range1. If you have multiple criteria, it doesn't matter which criteria you list first, second, and so on. Just be sure to define the criteria range 1 and criteria 1 describing the same condition, criteria range 2 and criteria 2 describing the same condition, and so on.
Let's go through some examples. See the screenshot below that contains data on our employees, clients, and billings.
Using SUMIFS, we could easily answer the question, "How much was billed to The Real Estate Group for work done by the Consulting department?" The first argument, sum_range, in this example is J2:J14 because these are the amounts billed the client that we want to sum. We have two conditions in order to answer this question — the client and the department. It doesn't matter which condition you list first and second. Let's start with the condition about the client. The second argument, criteria_range1, is E2:E14 because this is the range of cells that contains the client. The third argument, criteria1, is "The Real Estate Group" because this is the text we want to look for in our criteria_range1 (E2:E14). Now we can define our second condition, which is the department. The fourth argument, criteria_range2, is D2:D14 because this is the range of cells that contains the department. The fifth argument, criteria2, is "Consulting" because this is the text we want to look for in our criteria_range2 (D2:D14). Put together, the formula is =SUMIFS(J2:J14,E2:E14,"The Real Estate Group",D2:D14,"Consulting"), which calculates to $30,775.00, which is the total amount billed to The Real Estate Group for work done by the Consulting department.
With a small dataset like the one above, it may be easy enough to do this manually; however, the formula will work just as well if you had thousands of rows of data, when a manual process would be very inefficient and prone to error.
Let's look at another example. Using the same dataset listed above, let's answer the question, "How many billable hours were worked by employees with a billable rate of more than $175 per hour in the tax department on the client Sunglasses Limited?" The sum_range is G2:G14, the billable hours we want to sum. The criteria_range1 is I2:I14, the range of cells that contains the billable rate per hour. The criteria1 is ">175" because we want Excel to pick up any values greater than 175 in criteria_range1. The criteria_range2 is D2:D14 because this is the range of cells that contains the department. The criteria2 is "Tax" because this is the text we want Excel to look for in our criteria_range2. The criteria_range3 is E2:E14 because this is the range of cells that contains the client. The criteria3 is "Sunglasses Limited" because this is the text we want Excel to look for in our criteria_range3. Put together, the formula is =SUMIFS(G2:G14,I2:I14,">175",D2:D14,"Tax",E2:E14,"Sunglasses Limited"), which calculates to 62.50, the total amount of billable hours worked by employees with a billable rate of more than $175 per hour in the tax department on the client Sunglasses Limited.
Let's do one more example. Using the same dataset listed above, let's answer the question, "What are the total hours worked this week by employees in the Consulting department that billed more than $10,000 to the client?" The formula for this is =SUMIFS(F2:F14,D2:D14,"Consulting",J2:J14,">10000"), which calculates to 127.50. Note that I do not have a comma in the thousands place of 10000. Excel interprets the commas as a change in arguments.
About the author
Kelly L. Williams, CPA, Ph.D., MBA, is an associate professor of accounting at the Jones College of Business 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.
