Simple GoogleDocs formula help needed - COUNTIF

SideWinder · 9 Mar 2023 at 16:42

Hi,

I would like to count the number of 'entries' that have been inputted by status in Column C (Open,Cancelled,Complete), name in Column D (this is a list of users so around 5 people) plus I also want to count the date inputted, Column K.

For example, count for all of Feb or over a 2 week period. At the moment the formula doesn't include the date so its like this:

=COUNTIFS(D: D ,"David", C:C,"Open")

Any ideas on how I can add the count for the date and even change the the time period?

SideWinder · 13 Mar 2023 at 14:57

Thank you, will have a play around and see if this works out ok.

=COUNTIFS(D : D,"David",C:C,"Open",K:K,">="&1/3/2023,K:K,"<="&13/3/2023+7*2)

@t31os ^ I am trying this but unable to get it working. Is there a way to set it change it to be 'to present day' perhaps? So a set date to present?

I think I have got myself confused with the date part?

SideWinder · 14 Mar 2023 at 14:52

@t31os - Thank you so much for the reply, it is appreciated.

I think I have worked out the issue. Our GoogleSheets, in column K, has a date where you select the date from the calendar view so I think its not picking it up, does that sound right?

I am looking at your test document and comparing with the one I am using.

It did work with ">="&TODAY()-7) though - is this today's date minus 7?

Full code: K:K,">="&"1/3/2023",K:K, ">="&TODAY()-7)

SideWinder · 15 Mar 2023 at 09:44

t31os said:
Yes, today()-7 would be 7 days prior to today as one might expect.

Is your K column set to a number/date format? It should be(presumably anyway) fine to pull dates in via calendar, my gut says the data format needs setting for the column.

I think I have confused myself.

Full code: K:K,">="&"1/3/2023",K:K, ">="&TODAY()-7)

Surely this means it's counting 1st March - 15th March but then minus 7? When I do this, it counted 12 but then when I removed the -7 it only counted 1..which makes no sense as it should be more no?

Basically, I'd want it to count 1st March up until today. OR have the way to change it to be a 7 day rolling.

K is set to date format yes.

Sorry for all of these questions..I can perhaps send/upload the document but remove a lot of the data?