0

I have a column filled with dates and am trying to count all the cells after a certain date, using COUNTIF(A:A,">"&DATE(xx,xx,xx)). The problem is it keeps including my column header ("Date") in its count. Why is this happening, and how can I fix it?

I think part of the problem may be that I'm using a cell reference for the date, so my actual formula is COUNTIF(A:A,">"&B1), where B1 contains a date in Excel format ("DATE(xx,xx,xx)"). I checked and I don't have this issue when I simply put a date into my original formula.

I have no clue what's going on and any help would be appreciated! Thanks!

2
  • Just deduct 1 from the count?
    – JvdV
    Jan 6, 2021 at 23:42
  • Could you provide your sample Table? I tested with simple Date column with "Date" header, the formula works fine. Please provide more information.
    – Lee
    Jan 7, 2021 at 10:02

1 Answer 1

-1

COUNTIF only accept column ranges, you'll need to use INDIRECT to search in specific ranges.

=COUNTIF(INDIRECT("A2:A99"),">"&B2)

Notice how INDIRECT needs an explicit starting & ending row numbers.

5
  • This statement is simply false. Sorry for the downvote. I'd rather not. Maybe you can rectify?
    – JvdV
    Jan 6, 2021 at 23:36
  • @JvdV Which part is false? Jan 7, 2021 at 11:20
  • And your suggestion to subtract 1 assumes the comparison returns true, adding 1 to the total, but that's not guaranteed in other use cases. Jan 7, 2021 at 11:24
  • =COUNTIF(A2:A99,">"&B2) is a perfect syntax. There is absolutely no need to use INDIRECT() and even make this volatile. It's just .....incorrect. Sorry, but that's what it is.
    – JvdV
    Jan 7, 2021 at 11:25
  • I'll test it again, it didn't work for me on Excell Online. Jan 7, 2021 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.