COUNTIFS Returning Result Off By +1

Question

I am using two formulas to track data for today's date. They seem to be identical to me with the exception of the terms being searched in one of the columns. One formula returns the correct data while the other returns +1 to whatever the count should be.

Formula 1: =COUNTIFS(E2:E300, "NEW",B2:B300,TODAY())

Formula 2: =COUNTIFS(E2:E300, "USED",B2:B300,TODAY())

Formula 1 returns the correct value. I can add or removed however many cells with the word "NEW" and it does the correct count.

Formula 2 returns a value of 1 even if no cells in the column say "USED" and if there are "USED" present it counts them and still adds 1.

I can change everything to "NEW" and have no issues, but whether it says "USED" or is left blank, the second formula ends up off by +1.

Any help is much appreciated!

Answer 1

The two COUNTIFS formulas are structured correctly. If the second formula always returns one extra result, Excel is finding at least one row where column E evaluates to "USED" and the corresponding cell in column B contains today's date. Keep in mind that COUNTIFS also counts hidden or filtered rows, and a cell may display something different while a formula in that cell actually returns "USED".

To identify exactly which row is being counted, if you are using Microsoft 365, try:

=FILTER(ROW(E2:E300),(E2:E300="USED")*(B2:B300=TODAY()),"No matches")

This should return the row number(s) responsible for the count. I would also check for hidden rows and formulas in column E rather than only looking for manually entered "USED" values. If you can share a small sample of columns B and E (with any personal information removed), it should be possible to determine exactly where the extra count is coming from.

Answer 2

Is it possible you have a hidden row with the value USED?