[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Excel Formula

Hello, I have a database that captures all of the management information for technical issues captured.  One column, BA, calculats the amount of time from the start of a tech issue to when the support team was contacted.  I do this for multiple regions.  So I created a countifs formula that follows:
=COUNTIFS('IM Raw Data'!$H:$H,"HBUS",'IM Raw Data'!$BA:$BA,"15:00>=30:00",'IM Raw Data'!$J:$J,">="&"01/01/2014",'IM Raw Data'!$J:$J,"<="&"01/31/2014")

Bsically the formula first looks for the correct region, in this case HBUS, then I want to count the number of instances that occur in:
>=15, 30>=15, 60>=30, and >60.  Then I am looking for the corresponding beging  and ending month dates.

Not sure why this insn't working
  • 2
2 Solutions
Rob HensonIT & Database AssistantCommented:
A couple of questions:

Does the data in column BA have literally text "15:00>=30:00"? Or does it have numbers ranging from 15:00 to 30:00.

If the latter you will have to do the same as you have with the dates and have two criteria within the formula: ">=15:00" and "<=30:00".

Secondly, I often find that these formulas misinterpret dates within the criteria. I find it more reliable to put the dates that you require comparison with in a cell and then refer to the cell rather than a firm date.

Rob H
Glenn RayExcel VBA DeveloperCommented:
It looks like you're wanting to set up some aging buckets for response time like so:
< 15 minutes, 15-30 minutes, 30-60 minutes, > 60 minutes

If the values in column AB are indeed timevalues - that is, they are not text strings that look like time, but are entered in Excel like h:mm:ss - then you can create the COUNTIFS formula as follows:
=COUNTIFS('IM Raw Data'!$H:$H,"HBUS",'IM Raw Data'!$BA:$BA,">"&TIMEVALUE("0:15:00"),'IM Raw Data'!$BA:$BA,"<="&TIMEVALUE("0:30:00"),'IM Raw Data'!$J:$J,">="&DATEVALUE("01/01/2014"),'IM Raw Data'!$J:$J,"<="&DATEVALUE("01/31/2014"))

To make it more convenient to display the "buckets", you could set up a row or column with the different time values, a couple of cells for your start/end dates, and then point to them in the formula.  See the attached example file.

Rob HensonIT & Database AssistantCommented:
Alternatively, set up a pivot table to show a summary for all different regions and time stamps.

You can put date as a Page Filter and it should group the dates into months so you can select a specific month rather than having to select multiple entries for each day.

Rob H
Rrave26Author Commented:
Thanks guys, for the help, Both answers worked perfectly to get what I needed.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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