I am trying to use countif to count a certain number prior to today's date. So if today's date is 8/15/2019 the formula would count the number that is the B column that had a corresponding date prior to 8/15 or a formula that would do the same except greater than today's date. I used the date formula for today's date

Anyone have any idea? I am lost.

See screenshot below

2 Answers 2


If you're going to insert today's date into a cell you can use:

=COUNTIF(A2:A32, "<"& C13)

But I'd say just use a formula to calculate 30 days from today's date. The formula to use is:

=COUNTIF(A2:A32, "<"& TODAY()-30)

Or if your dates continue down the column you can do from the starting date in A2 all the way down the column even if you add extra rows later. That would look like:

=COUNTIF($A2:$A, "<"& TODAY()-30)

First thing to have in mind is that Google Sheets (as well as other spreadsheet apps) use serial numbers to handle dates, so when a value looks like 8/15/2019 (if it's handled as date and not as text) Google Sheets internally handle this value as 43692.

Recarding COUNTIF, this function requires two arguments and if you will use a comparison operator like < it should be quote enclosed.

The formula to use is:

=COUNTIF(A2:A32, "<"& C13)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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