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

Cybersecurity threats proliferating for midsize and smaller businesses

This report details how SMBs can properly protect private information from breaches, design and implement a cybersecurity policy, and create safeguards for training and education.

QUIZ

News quiz: Senate health care bill in the spotlight

Reports related to the Republican bill to repeal many provisions of the PPACA, other tax issues, and the giant AICPA ENGAGE Conference offered a diverse reading list for June. See how much you know about recent news with this short quiz.