Excel Formula

Posted on 2014-08-06
Last Modified: 2014-08-07
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
Question by:Rrave26
    LVL 31

    Accepted Solution

    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
    LVL 27

    Assisted Solution

    by:Glenn Ray
    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.

    LVL 31

    Expert Comment

    by:Rob Henson
    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

    Author Closing Comment

    Thanks guys, for the help, Both answers worked perfectly to get what I needed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now