Search code examples
google-sheetsgoogle-sheets-formulareporting

In Google Sheets, count a row when date is in a certain range while range is in two different columns

In Google Sheets I would like to:

  • count the number of rows in a column
  • where a given date
  • is greater than or equal to the date in column "in progress"
  • is smaller than or equal to the date in column "closed"

A dataset example:

In progress Closed
09/02/2019  10/02/2019
10/02/2019  11/02/2019
11/02/2019  11/02/2019
11/02/2019  12/02/2019
12/02/2019  13/02/2019  
05/02/2019  18/02/2019

I would like to set a certain date and count the rows that are within that range.

E.g. date = 11/02/2019 would give the following output:

In progress Closed      Expected Outcome
09/02/2019  10/02/2019  0
10/02/2019  11/02/2019  1
11/02/2019  11/02/2019  1
11/02/2019  12/02/2019  1
12/02/2019  13/02/2019  0
05/02/2019  18/02/2019  1

In my sheet I have two columns and I expect something like this based on above data:

Date         Work in Progress
10/02/2019   3
11/02/2019   4
12/02/2019   3
13/02/2019   2
20/02/2019   0

How can I achieve this in Google Sheets?

I've tried a lot of different functions and aggregates, but nothing gives me the desired result. COUNT only works for one row, COUNTIF only seems to accept a range within 1 column and combinations of IF, SUM, AND, COUNT, COUNTIF all don't return the right values.

Solution

  • Provided A is your "in progress" column, B is your "Closed" column, and C is your "Date" column, I think this will give you what you want

    =countifs(A:A,"<="&C6,B:B,">="&C6)

    er