Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

How to set up a search function which will poplute a series of cells based on Countifs function

I am trying to populate a series of calendar/time cells which show whether a test activity was being performed based a countifs formula.  

In columns c and d I have the start date/time and end date/time of each test activity.

In columns h and I have a the start and end dates/times (in one minute increments) that I want to determine if there was test activity in.

In column j I have a formula

 =COUNTIFS(StartDate,">="&H6,EndDate,"<="&I6)

that I intend will go compare each of the test events for to see if they happened on or after the calendar date/time in column h and on or before the date/time in column I.  The intent is to populate column j with true values when there was test activity during that 1 minute increment for all of the dates/times listed in columns h and I.

This is probably simple and I'm overlooking something.

Thanks for your help.

Jim
CountifsProblem.xlsx
0
vistauser
Asked:
vistauser
  • 2
  • 2
1 Solution
 
mankowitzCommented:
I think what you are saying is this:

I want column J to be TRUE if there is an overlap between the timeframes defined by
1. Column D to Column D
2. Column H to Column I

If so, try this formula for J6 and copy down

=AND(I6>TestData[[#This Row],[Start date]],H6<TestData[[#This Row],[End date]])

Please note that in your sample data, I did not see any overlaps.
0
 
vistauserAuthor Commented:
I'm sorry.  I probably didn't explain the question well.  I am not looking for overlaps.

I am looking to see if the time defined in each set of cells: c6-d6, c7-d7; etc. happened in the times listed in the time ranges defined in cells hx (start time) - ix (end time).  There are definitely times in the time range that I have defined in h and I but you have to go down to row 724 or so.

The intent is that I will have a "calendar" of 1 minute increments in h and I that shows when test activity was taking place.

Jim
0
 
mankowitzCommented:
ok, in that case, I think you want this:
=COUNTIFS(TestData[Start date], "<=" &I6, TestData[End date], ">=" & H6)
Hrs-Diff-Formula.xlsx
0
 
vistauserAuthor Commented:
Thanks for your help and sorry for the delay in the response to show this as "solved".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now