SUMPRODUCT w/ 2 Date Ranges + 2 Conditions

[thomas_fcc]

Hi everyone,

I've been having quite a bit of difficulty with this formula i'm working on, and here are the details!

1) In my work book i have 3 spreadsheets.
On the 1st and 2nd spreadsheet, in the F column, the agent puts the day (mm/dd/yyyy) that the call was made on, then there name in column G.

2) On my 3rd spreadsheet, i have a calendar control object, which when the wanted date is selected, it will pop it up in cells O12 & P12. The calendar control works 100%. This way i can track for one day at a time.

3) On this same 3 spreadsheet, theres a list of the agents who filled the call names in on the other 2 spreadsheets (column G), and my goal is to be able to pick a date on the calendar, and it will display the total calls for that day for that person (by going off the date range i pick), and below it, a total for everyone.

Here's my code for these agents;

=SUMPRODUCT((('May Expiry 09'!F2:F700>=O12)*('May Expiry 09'!F2:F700<=P12)+('April Expiry 09'!F2:F700>=O12)*('April Expiry 09'!F2:F700<=P12)),('April Expiry 09'!G2:G700="NAME")+('May Expiry 09'!G2:G700="NAME"))
Date from, to, for both sheets, then the condition for each sheet.

Whats happening is that my totals code i had mentionned (which is;

=SUMPRODUCT(('May Expiry 09'!F2:F700>=O12)*('May Expiry 09'!F2:F700<=P12)+('April Expiry 09'!F2:F700>=O12)*('April Expiry 09'!F2:F700<=P12)) )

is 100% accurate, however the individual totals are counting cells that are not even linking to their name or the right date! I know theres something wrong because if i pick a date, the sum of the individuals should equal my total balance. Some days its accurate, and some aren't.

I've been trying to figure this out for a while now and i'm having a little trouble.

Could anyone help me please? If more information is required, please let me know.

Thanks!

 

[thomas_fcc]

Bump..

 

[barry houdini]

Hello thomas, welcome to MrExcel,

You need to combine the conditions differently to avoid erroneous results, try

=SUMPRODUCT(('May Expiry 09'!F2:F700>=O12)*('May Expiry 09'!F2:F700<=P12)*('May Expiry 09'!G2:G700="NAME")+('April Expiry 09'!F2:F700>=O12)*('April Expiry 09'!F2:F700<=P12)*('April Expiry 09'!G2:G700="NAME"))

 

[thomas_fcc]

Barry_Houdini,

It works amazingly! I've spent hours tinkering with this; you have no idea how much your help has been appreciated!

I wouldn't suppose you know why it must be combined differently, and how excel calculates these formulas?

Thanks again! Your the man!