0

Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:

     A         B
1    27.02.19  15.03.19
2    03.03.19  05.03.19
3    01.04.19  05.04.19

What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.

What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.

|improve this question

migrated from superuser.com Mar 22 at 13:24

This question came from our site for computer enthusiasts and power users.

  • I don't see that date in rows 1 and 2.... – Els den Iep Mar 12 at 12:21
  • given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2 If C1=04.03.19 , then =COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1) – Akina Mar 12 at 12:56
  • @ElsdenIep 04.03.19 is included between the range given starting A1 and ending B1, as well as between A2 and B2. – KaffeeKnollen Mar 12 at 14:06
0

For my solution you will need another column, based on the next formula:

E1 = 04.03.2019 # The Date you want to find

=If(AND($E$1>=A2;$E$1<=B2);True;False)

Now we count

=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed

I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.

I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.

|improve this answer
  • How will this work for an indefinite number of rows? I need to check if the date is between A1 and B1, and then A2 and B2, so on until AN and BN I'm trying to do this for all dates of the year, so if I have 365 days, I'd prefer to not have 365 colums if possible – KaffeeKnollen Mar 12 at 14:21
  • Huh?! This works for an undefined range. You just need to drag(double click) the formula, so it will fill the whole column. The "C:C" in COUNTIF() means the whole column. I did tested it. – dmb Mar 12 at 14:25
  • @KaffeeKnollen Also, that's one important piece of information right there. How can we know from the question that you are going to check every date of a year? As stated this answer your question. – dmb Mar 12 at 14:28
  • You are right, I should have stated that earlier, sorry. Your solution still works since there are still a fixed number of columns (or rows, depending), it's just a lot of them, so I wonder if there's a way to prevent that. – KaffeeKnollen Mar 12 at 14:34
  • @KaffeeKnollen I just can't come up with another method, you can try a really convoluted formula, or make a pivot table as a cheat sheet with the date as a header. So instead of looking at your large dataset, you can simple check the count by column. Give it a time and maybe someone will come up with another idea. – dmb Mar 12 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy