Page 265 - JoFA_2022

Using SUMIFS, we could easily answer the many billable hours were worked by employees with
About the question, “How much was billed to The Real Estate a billable rate of more than $175 per hour in the tax
author Group for work done by the Consulting depart- department on the client Sunglasses Limited?” The
ment?” The first argument, sum_range, in this sum_range is G2:G14, the billable hours we want
Kelly L. Williams, example is J2:J14 because these are the amounts to sum. The criteria_range1 is I2:I14, the range of
CPA, Ph.D., MBA, billed the client that we want to sum. We have two cells that contains the billable rate per hour. The
is an associate conditions in order to answer this question — the criteria1 is “>175” because we want Excel to pick up
professor of client and the department. It doesn’t matter which any values greater than 175 in criteria_range1. The
accounting at the condition you list first and second. Let’s start with criteria_range2 is D2:D14 because this is the range
Jones College of the condition about the client. The second argu- of cells that contains the department. The criteria2
Business at Middle ment, criteria_range1, is E2:E14 because this is is “Tax” because this is the text we want Excel to
Tennessee State the range of cells that contains the client. The third look for in our criteria_range2. The criteria_range3
University. argument, criteria1, is “The Real Estate Group” is E2:E14 because this is the range of cells that
because this is the text we want to look for in our contains the client. The criteria3 is “Sunglasses
criteria_range1 (E2:E14). Now we can define Limited” because this is the text we want Excel to
our second condition, which is the department. look for in our criteria_range3. Put together, the
The fourth argument, criteria_range2, is D2:D14 formula is =SUMIFS(G2:G14,I2:I14,">175",D2:D14,"
because this is the range of cells that contains the Tax",E2:E14,"Sunglasses Limited"), which calculates
department. The fifth argument, criteria2, is “Con- to 62.50, the total amount of billable hours worked
sulting” because this is the text we want to look for by employees with a billable rate of more than
in our criteria_range2 (D2:D14). Put together, the $175 per hour in the tax department on the client
formula is =SUMIFS(J2:J14,E2:E14,"The Real Estate Sunglasses Limited.
Group",D2:D14,"Consulting"), which calculates to Let’s do one more example. Using the same da-
$30,775.00, which is the total amount billed to The taset listed above, let’s answer the question, “What
Real Estate Group for work done by the Consult- are the total hours worked this week by employees
ing department. in the Consulting department that billed more
With a small dataset like the one above, it may than $10,000 to the client?” The formula for
be easy enough to do this manually; however, the this is =SUMIFS(F2:F14,D2:D14,"Consulting",J2
formula will work just as well if you had thousands :J14,">10000"), which calculates to 127.50. Note
of rows of data, when a manual process would be that I do not have a comma in the thousands place
very inefficient and prone to error. of 10000. Excel interprets the commas as a change
Let’s look at another example. Using the same in arguments.
dataset listed above, let’s answer the question, “How — By Kelly L. Williams, CPA, Ph.D. ■

journalofaccountancy.com June 2022 | 49