selecting multiple criterias to return one variable

**mariasyrny** · 02-05-2009, 06:26 PM

Hello,

Hope anyone can help...I have been trying to crack this one for over a week and after several headaches...I have decided that I do need expert help.

I have a worksheet that I'm building as a template. It looks liek this:

Date: 1/26/2009

TEAM GREEN
Agent Name 8:00 8:30 9:00 9:30 10:00 10:30Bruce
No. of Calls
Ready Time
Talk Time
Break Time 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 Total 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00 0:00:00

In another worksheet I have raw data for 5 days containing the no. of calls, ready time and talk time for all the agents in half an hour intervals. I want to enter a formula next to the no. of call (for example) that will match the date, the agent name and the time interval and returns the no.of calls value.

One important things is that the information on the "data" worksheet is all listed under the same row. For example:
1/26/09 8:00 Bruce 358 02:45:00 15:50:12

Anything anyone can offer will be greatly appreacited. I woudl rather not manipulate too much the "data" worksheet because I'm trying to use it as raw as it comes from CISCO reporting.

Thanks,
M.

**JBeaucaire** · 02-05-2009, 07:33 PM

I imagine a sumproduct will do the count for you. There's not enough data there to give a working example (care to upload a workbook to make that simple?), but here's the structure:

=SUMPRODUCT(--(DateRangeCells=DateCell),--(ReadyTimeRangeCells>=StartTimeIntervalCell),--(ReadyTimeRangeCells<=EndTimeIntervalCell),--(AgentNameRangeCells=AgentNameCell))

See if you can piece in the proper ranges. remember, they all have to be the same size in rows for this to work, like:

=SUMPRODUCT(--(Sheet1!A2:A100=Sheet2!A1),--(Sheet1!B2:B100>=Sheet2!B2),--(Sheet1!B2:B100<=Sheet2!C2),--(Sheet1!C2:C100=Sheet2!D2))

**mariasyrny** · 02-06-2009, 10:17 AM

Thank you for your response...I will try it. I'm getting to the point that I think it is better to do this in Access. But attached is the file to make it easier.

What I'm trying to do is match from the Wkly Stat worksheet cells A5,B1,C4 to the Data worksheet and return, in the Ready Time cell (C7) the time from the Data sheet. The same formula will be used for Talk Time and through all interval periods. No of Calls will come from another sheet all together (not attached) and I can get that with a simple vlookup.

I hope you can help...because I don't kow much Access and finding a resource to help me put this together is not easy.

Thanks,
Maria

**JBeaucaire** · 02-06-2009, 04:19 PM

There is a disconnect with the types of data you have. It's all formatted as time and looks the same, but it's different. If you want these formulas to work you'll need to match the data types in more than just appearance.

Here's your sheet with the No. of call working...I couldn't figure out the break stuff, and C20 is referenced across the bottom row but has no value, so I just put a 1 there for now.

I also installed a UDF called IFERROR to make error checking simpler, like in column A where we needed the VALUE# errors to not occur or it would break the SUMPRODUCT formulas.