# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Finance AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Excel 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.

-Glenn
EE-TechResponse.xlsx
0
Finance AnalystCommented:
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 Commented:
Thanks guys, for the help, Both answers worked perfectly to get what I needed.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.