thomas_fcc
New Member
- Joined
- Jun 5, 2009
- Messages
- 3
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!
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!
Last edited: