Simple GoogleDocs formula help needed - COUNTIF

SideWinder

SideWinder

SideWinder

Consigliere
Joined
12 Jun 2004
Posts
151,029
Location
SW17
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?
 
Last edited:
If i'm understanding correct you want to count where the date is within a range in column K.

An example to match dates from Jan 1st to the 10th (without reference cells - and ignoring lazy example year).

Code: 
=COUNTIFS(K:K,">="&"1/1/1970",K:K,"<="&"1/10/1970")

You can of course use cell references for start and end dates.

Code: 
=COUNTIFS(K:K,">="&Z1,K:K,"<="&Z1)

Where Z1 and Z2 would be your start and end dates (change as appropriate of course).

Using a start date without the need for an end date cell, you can calculate the end date using your reference cell (2 weeks in this example).

Code: 
=COUNTIFS(K:K,">="&Z1,K:K,"<="&Z1+7*2)

All of this would naturally be in addition to your existing countifs criteria, so as a complete example using just a start date reference cell (because why wouldn't you use a reference cell).

Code: 
=COUNTIFS(D:D,"David",C:C,"Open",K:K,">="&Z1,K:K,"<="&Z1+7*2)

EDIT: That will actually result in a 15 day period if the start date is included, you'll probably want the last operator to be less than "<" instead of less than equals "<=" to get 14 days including the start date.
 
Last edited:
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? :o
 
Last edited:
You should be able to use todays date using...

Code: 
&TODAY()

When using a literal string value for the date (no reference cell / function call) you'll need quotes around the date if i'm not mistaken.

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

You can add days using the today function.

Code: 
">="&TODAY()+7

And go the other way to..

Code: 
">="&TODAY()-7

Very simple test sheet.

docs.google.com

excel help

docs.google.com docs.google.com
 
Last edited:
@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)
 
Last edited:
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.
 
Last edited:
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.
Click to expand...
I think I have confused myself. :o

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?
 
This is what i assume you actually want, a two week date range based on the current day.

Code: 
K:K,">"&TODAY()-14,K:K, "<="&TODAY()
..or..
Code: 
K:K,">="&TODAY(),K:K, "<"&TODAY()+14

If i'm misunderstanding, feel free to link some sample data (no need to show me your real data).

If you want to use a fixed/literal date, it's also possible to use addition/subtraction with those to.

Code: 
K:K,">="&"01/03/2023",K:K, "<"&"01/03/2023"+14
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom