Hi - Dave here.
Last week I sent out an article called
Why SUMPRODUCT?
This week, I want to followup with a specific example of a problem you can solve with SUMPRODUCT that you can't solve with COUNTIFS.
In the worksheet below, the goal is to count orders (rows) where the state is Texas ("TX"), the amount is greater than $100, and the month is March.
You would think that COUNTIFS would be the perfect tool for this job, but the problem is that COUNTIFS won't let you use the MONTH function like this:
=COUNTIFS(E5:E15,"tx",C5:C15,">100",MONTH(F5:F15),3) // fails!
The MONTH function works fine in SUMPRODUCT, as you can see below.
[
download the workbook and read the full explanation]
The article provides a complete explanation and includes the Excel workbook used in the example. Download the workbook and try it out yourself.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
