The Aftermath of Many Conditions


Q: I use COUNTIF and SUMIF to summarize detailed transactions based on criteria for one column, but I need to count and sum transaction data based on criteria for multiple columns. Is there an easy way to do this?


A: Beginning with Excel 2007, Microsoft added the new functions SUMIFS and COUNTIFS, which are the plural equivalents to SUMIF and COUNTIF. These new functions each allow you to sum or count based on up to 127 criteria. The proper syntax for using these functions follows:


SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)


COUNTIFS(count_range, criteria_range1, criteria1, criteria_range2, criteria2, …)


For example, the worksheet in the next column lists 100 rows of sales transactions, beginning at row 12. The highlighted SUMIFS formula in cell E6 calculates the total sales based on two criteria as follows: (1) sales generated by Claire, and (2) sales resulting in a profit margin of less than 15%.


Note: I could have inserted the criteria (Claire and < 15%) directly into the SUMIFS formula. Instead, I listed these criteria (along with other criteria) in a table format, and referenced the cells containing the criteria in the SUMIFS formula. This approach allows me to then copy the formula in cell E6 to the range E6…H9 (with slight edits to the formulas in columns F and G) to produce a summary table of values using the SUMIFS function.


Advanced tip: You can use the question mark (?) and asterisk (*) as wildcard characters in your criteria; a question mark matches any single character, and an asterisk matches any sequence of characters. For example, to sum sales for those transactions resulting in less than 15% profit, which also occurred in either the Northeast or Southeast, I used the criteria *east, as shown.


More from the JofA:

 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos


2019 State of Financial Reporting Survey

We surveyed nearly 600 finance and accounting professionals on their month-end close and reporting processes. See the results.


What RPA is and how it works

Robotic process automation is like an Excel macro that can work on multiple applications, says Danielle Supkis Cheek, CPA. RPA can complete routine, repetitive tasks such as data entry, freeing up employee time from lower-level chores.