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


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.