9. Multiple conditions for SUMIF
Let’s say a table has a dollar column you want to add up, but only for entries that have “Industry” = “Finance” whose “Lead Underwriter” is “Morgan Stanley”. You could set up Filter and then use SUBTOTAL, as described above. Another way to get the answer is using a type of SUMIF … the type with multiple conditions.
To give multiple conditions to a function such as SUMIF or COUNTIF, use the functions SUMIFS or COUNTIFS instead. The general syntax is SUMIFS(dollar column to be summed, column of name1s, name1 criterion, column of name2s, name2 criterion,…) as shown in the screen capture below.
10. Weed out bad parameters early with Data Validation
Let’s say the financing table has a column of dates and you want to sum only those with certain dates. You want to set up the SUMIF with a criterion that is forced to be a date. Use the Data Validation tool , which can also be found on the Data ribbon. (Note: keyboard shortcut is Alt + A + V + V). This is what the dialog box looks like, with “Date” format selected. (I have circled the applicable cell, button, and list-choice.)
If you choose date format, then you can set the range of dates, such as values only for the January 2006 to 2015.
When constraining the input, to guide the user, Zelman advised us to set up appropriate warnings and error messages to guide the user who encounters a Data Validation message.
Here’s a good error message, that even includes sample input:
Zelman emphasized that it’s always worth the effort to give a helpful error message, even if you think it’s a temporary kludge. ª