Lookup formula required to search for two criteria and return result

[dave1983]

I am trying to get a formula to lookup TWO search criteria and then return one result, I have a spreadsheet with Data for a whole month so I want to be able to change the two search criteria to return the desired result.

For Example if I want to find the amount of calls taken by JOHN A on the 01/11/12 I want the result to be 5

But if I change the search criteria and Again search for JOHN A but change the date to 02/11/12 I want the result to be 10

01/11/2012

John A 5
Lorraine A 23

02/11/2012

John A 10
Lorraine A 14

All my raw data is on another sheet with the name of the person and then the date as shown above but for a whole month (that’s where I am struggling).

I have attached a copy of the workbook just incase my description is not clear, Basically cells B3 & C2 can be changed to what ever the user wants to look for.

[tigeravatar]

dave1983,

Attached is a modified version of your posted workbook.
In cell C3 on sheet 'Calls Taken Per Agent Per Day' is this formula:

Please Login or Register  to view this content.

[dave1983]

That's great, that is exactly what I needed many thanks for your help.

[tigeravatar]

You're very welcome

[dave1983]

I wonder If I can be cheeky, I have a lot of formula in place in the attached workbook but there seems to be an issue with certain parts of it and not others and as much as I have tried to correct the issues to date I have been unsuccessful.

I just cant think what else to try, maybe need to go at it another way.

Any help would be much appreciated

Thanks

Dave

[tigeravatar]

Dave,

The results in your most recent posted workbook all seem to be correct. What issues are you experiencing?
As for optimizing the workbook, there's a lot of good information about how to that here:
http://www.ozgrid.com/forum/showthread.php?t=76234
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

[dave1983]

I will look at those now, The issues I have for Example are

In the 'Monthly Calls Answered Raw Data' tab Hannah Connor has taken 1 call on the 02/11/12

But in the 'Monthly Calls Sorted By Agent' tab it is not returning the number of calls it just shows as blank or nothing due to the Iferror formula but should show as 1 call, the rest of the month is exactly the same as even on days when calls were taken it shows nothing.

That may be reason why the 'Calls Taken Per Team' tab is not pulling the information.

The error I assume must be in the Monthly Calls Sorted By Agent' tab, I added an extra tab with the details sorted by name as thought this may be the issue but that didnt resolve it.

[dave1983]

I have just noticed that It is finding the first example for someone and then the next day instead of searching for them again it is just putting in the data 2 collumns along and not actually searching for the correct data.

For example as per the attached workbook

If you search for Abel Gainer on the 01/11/12 he took 15 calls and it correctly returns cell B39 as the result, on the 02/11/12 he didnt take any calls so the result should be 0 but its actually returning cell reference D39 and then the 03/11/12 E39 but not the correct result which should be E54 which is 20.

Any ideas.