COUNTIFS and SUMPRODUCT are giving different results

Question

Context: I am working on an Excel 2010 spreadsheet, summarizing a lot of data and deriving various results by comparing the contents of several columns. All of the details aren't important; I'll provide a simplified actual example of the issue.

I have formulas that work based on using SUMPRODUCT. I believe I should be able to achieve the same results using formulas based on COUNTIFS. However, when I try it, the COUNTIFS formulas do not produce the correct results, and I'm trying to understand why that is. Here is an example:

The source data is in columns A, B, and C. I'm summarizing based on column A, labelled Active Days.

Column E is a list of the different numbers of Active Days, which can range from 1 to 31. The values in columns F through H are the summary figures associated with each number of active days. The example was extracted from a large data set, so not every number of active days has data showing.

Column F is just a count of records having that number of active days.

Columns G and H are one example of a derived result. Column G was calculated using my COUNTIFS formula, column H using my SUMPRODUCT formula. All column H values are correct. The column G values sometimes match by coincidence, but the formula is not producing the expected result.

I'll use the SUMPRODUCT formula to explain the calculation. The formulas for the last row are annotated on the image, so I'll refer to those:

=SUMPRODUCT(($B$3:$B$1001<>"")*($B$3:$B$1001<$C$3:$C$1001)*($A$3:$A$1001=E33))

The column B values are being filtered for non-blank. Records are being selected where the column B date is earlier than the column C date. And it is selecting only records whose column A value matches the last value in column E.

In this case, the last four data records in column A match 31 active days, none of those is blank in column B so they are not excluded, and none of them has an earlier date in column B than in column C. So the result is zero.

The formula for the COUNTIFS version in G33 is:

=COUNTIFS($B$3:$B$33,"<"&$C$3:$C$33,$A$3:$A$33,E33)

This does not explicitly filter out blanks in column B, but the rest is translation of the SUMPRODUCT logic to the COUNTIFS structure. For records with 31 active days, there are no blanks in column B so that difference shouldn't matter. Yet that is one of the non-matching results.

If anyone wants to examine the spreadsheet, I've saved it to this Google Sheets link called Error. The first tab is a large extraction of the actual file. There are several tabs named Not Working, that are small subsets of the data that I have been using to try to diagnose what's going on. The example above was extracted and reduced from one of the Not Working tabs and includes just one derived column; it is on a tab called Sheet1.

My question is why does the COUNTIFS version of the formula not produce the same result?

Answer 1

Why doesn't COUNTIFS produce the same correct result as SUMPRODUCT?

COUNTIFS is an expansion of COUNTIF in the sense that you can combine multiple criteria, but it is subject to the same kinds of rules that apply to COUNTIF. The basic problem with the COUNTIFS formulas in your spreadsheet is that COUNTIF can compare a range to a single criterion, but not a range to a range. That's why you're getting strange results with the COUNTIFS version.

To compare a range to a range, you need SUMPRODUCT. You can do it entirely with SUMPRODUCT, as you did, or you can use SUMPRODUCT in combination with COUNTIF to do some limited comparisons, such as finding unique values and duplicates. There is a good tutorial on this at Ablebits.com, including how to handle a lot of common problems. Coverage of using COUNTIF with SUMPRODUCT starts about halfway down the page.

One other characteristic to be aware of is that COUNTIF and SUMPRODUCT don't handle ranges in the same way. SUMPRODUCT calculates on a row-by-row basis. COUNTIF acts more in the aggregate. So for a task like finding matching values between columns, SUMPRODUCT will see a match only if the values on the same row match, while COUNTIF will see a match anywhere in the range.

There are also some other ways to compare ranges, such as using a combination of SUM and IF in an array formula. But different approaches to replicate the correct results you got using SUMPRODUCT is a different question for another day.