Solved

# Excel Formula

Posted on 2014-08-06
246 Views
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
0
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.

Thanks
Rob H
0

LVL 27

Assisted Solution

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.

-Glenn
EE-TechResponse.xlsx
0

LVL 31

Expert Comment

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.

Thanks
Rob H
0

Author Closing Comment

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

## Featured Post

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.