Countif with Dates

Helena P.

HI all,

I'd like to ask your help. I am trying to count the number of items which are planned for particular dates, so the formula should be easy yet.. I get "0" which is incorrect.

I need this for my metric to later use it for burn up chart.

=COUNTIFS({W2 System Test Tracker Range 1}, [Column2]$2)

the bit "W2 System Test Tracker Range 1" references to other sheet looking at the column with dates provided by the user, and "[Column2]$2" it's referencing a cell with date I am looking for.

Please see the screenshot:

hope this makes sense.

bisaacs

Hey @Helena P.,

Can you confirm that both the reference cell and the cell range are Date columns/the same column type? Column types have to match to ensure that the COUNTIF statement can compare the data correctly.

Hope this helps!

Helena P.

HI @bisaacs

thank you for prompt reply! I did wonder about it and the answer is no.

The metric sheet columns are setup as text/number. I will change this and see if helped :)

Helena P.

ok, so did this quick and I got error "Date Expected".

bisaacs

Hey @Helena P.,

So you're getting a "Date Expected" error (I'm assuming), because you have numbers below the dates (vs the whole column entry data being dates). What I might do if you don't want to completely reorganize the sheet is change the Column 2 type to text/number, that way both the System Tracker range of columns and the reference column are the same data type. See if that works!

Helena P.

Hi @bisaacs

that was my worry 🤣 and I think I am better of with rearranging the sheet as we do need to "enforce" the users to populate the dates only in the sheet I am referring too. i'll work on this and get back to you :)

thank you!

Helena P.

ok, I changed the column type to date and still have an error.. is it possible I get an error because the date I am looking at is calculated with a formula not typed in?

bisaacs

Hey @Helena P.,

Just to confirm, both the range of columns and Column 2 are Date columns? What error are you getting?

Helena P.

that is correct, both are now setup as date columns and for those where there are items meeting the condition I get error #date expected, for those where there is no items meeting the condition I get blank instead "0"…

Helena P.

HI @bisaacs

hope you are well. I managed to solve the issue by flipping the table, however encountered another challenge as i need to count the amount of items meetings more then 2 condition now, where first is the particular date and second condition is the particular status.

Please see the formula:

=COUNTIF({W2 System Test Tracker Range 2}, $[Column2]@row, {W2 System Test Tracker Range 3}, "05 Scenario Approved")

would you be able to help? I wonder if this issue is as I try to count item looking ad the date and something else in different info type.

bisaacs

Hey @Helena P.,

If you want to count something using multiple criteria, then you'll want to use COUNTIFS instead of the singular COUNTIF. COUNTIFS only counts a cell if something meets all criteria provided in the formula.

If you want COUNTIF to count cell if they met either/or certain criteria, when you enter the criteria, you'll put an OR statement there and use "@cell" in the OR statement, like this:

=COUNTIF(Test:Test, OR(@cell = 123, @cell = 576))

Hope this helps!

Helena P.

HI , my bad :S silly mistake! I didn't notice I was missing "S" :D all is working now!