Excel SUMIF and COUNTIF Complete Guide: Wildcards, Dates, and Multiple Criteria

This practical guide to Excel SUMIF COUNTIF includes copy-and-paste-ready tables and formulas for date ranges, wildcards, multiple criteria, and fixing mismatched range sizes.

Quick Fix: 3-Minute Recipes

=SUMIFS(Sales[Revenue], Sales[Date], ">="&TODAY()-7)

=SUMIFS(Sales[Revenue], Sales[Item], "*Shirt*")

=SUMIFS(Sales[Revenue], Sales[Channel], "Online", Sales[Category], "Shoes")

=COUNTIF(Sales[Note], "<>")

Concepts: SUMIF/COUNTIF vs. SUMIFS/COUNTIFS

• SUMIF/COUNTIF = one criterion; SUMIFS/COUNTIFS = multiple criteria (AND)
• All criteria ranges must be the same size
• For date comparisons, concatenate text: ">="&DATE(…)

Practical Examples (Sales Table)

Sum for a Date Range

=SUMIFS(Sales[Revenue], Sales[Date], ">="&DATE(2025,9,1), Sales[Date], "<="&DATE(2025,9,3))

Partial Matches/Escaping

=SUMIFS(Sales[Revenue], Sales[Item], "*Shirt*")

=COUNTIF(Sales[Note], "*~**")

Multiple Criteria

=SUMIFS(Sales[Revenue], Sales[Channel], "Online", Sales[Category], "Shoes")

Blank/Nonblank Cells

=COUNTIF(Sales[Note], "<>")

Monthly Total

=SUMIFS(Sales[Revenue], Sales[Date], ">="&DATE(2025,9,1), Sales[Date], "<"&EOMONTH(DATE(2025,9,1),0)+1)

Absolute References in Regular Ranges

=SUMIFS($F$3:$F$12, $A$3:$A$12, ">="&DATE(2025,9,1), $A$3:$A$12, "<="&DATE(2025,9,3))

Checklist

• Convert the data to a table with Ctrl+T
• Concatenate date criteria as text
• Wildcards: * / ? / ~
• Keep all criteria ranges the same size

Troubleshooting

Conclusion

You can now accurately handle dates, partial matches, and multiple criteria with SUMIF/COUNTIF. The next article covers OR and complex logic with SUMPRODUCT/FILTER.