Hi - Dave here.
This week, another example where COUNTIF can't be used, but SUMPRODUCT works just fine.
In the worksheet below, we want to count cells that contain errors. You might think we can use COUNTIF like this:
=COUNTIF(ISERROR(data),TRUE) // fails
The idea here is that the ISERROR function will return TRUE or FALSE, and COUNTIF will count the TRUE results. However, if you try to enter this formula, Excel won't let you. This is another case where COUNTIF won't work because it won't allow an "array operation" in place of a normal range.
The solution is to use SUMPRODUCT as seen below. Note again we are using a double negative (--) to convert the TRUE and FALSE values from ISERROR into 1s and 0s.
[
download the workbook and read the full explanation]
The article provides a detailed 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
PS - I know I've been talking a lot about the
SUMPRODUCT function lately. I want to make sure you understand how useful and versatile this function is, and how often you can use it to solve tricky problems in a simple, elegant way.
