The Aftermath of Many Conditions

BY J. CARLTON COLLINS

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

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.