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!

Improve this question
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

Reset to default
-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.

Improve this answer
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?
    – MichaelHabib
    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.
    – MichaelHabib
    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.
    – MichaelHabib
    Jan 7, 2021 at 13:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .