Solved

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

Posted on 2014-12-15
4
59 Views
Last Modified: 2014-12-20
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
Comment
Question by:vistauser
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 40501131
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
 

Author Comment

by:vistauser
ID: 40501150
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
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 40501262
ok, in that case, I think you want this:
=COUNTIFS(TestData[Start date], "<=" &I6, TestData[End date], ">=" & H6)
Hrs-Diff-Formula.xlsx
0
 

Author Closing Comment

by:vistauser
ID: 40510834
Thanks for your help and sorry for the delay in the response to show this as "solved".
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VBA name newly created sheet 4 24
Excel filter on tab not showing any entries? 5 23
sort column using vba 2 19
Problem to file 5 17
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question