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?