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

Revenue recognition: A complex effort

Implementing the new standard requires careful judgment. Learn how to make significant accounting judgments and document them and collaborate with peers for consistent application.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

News quiz: Taking an economic snapshot and looking to the future

Recent news included IRS actions that affect individuals and partnerships and a possibly influential move by a Big Four accounting firm.Take this short quiz to see how much you know about the news.