|
Formula
I have a worksheet that has a lot of information in it. I am trying to find a
formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
Try this:
With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
It worked thanks for your help.
I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
Maybe this?
With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
It helps a lot.
As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
OK....Try this:
With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
I'm still struggling with it. I have not tried the campaign formula yet. I am
still working on the date range with agent ID. The formula is working for almost all of the agents. I have 2 at the bottom that it will not bring their totals over. I have tried everything I know how to do and it will not work. I have also tried putting their ID in another cell that is calculating and it will not pull over the data. I tried changing the ID's around with other agents that are working and i get nothing. "Ron Coderre" wrote: OK....Try this: With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
Hi, Kelly
This is just a guess..... See if those agents have leading or trailing spaces or..are their ID's actually numbers, where the others are "numeric text", or vice versa. (a quick test for this: click the Centering button on then off..if the value aligns right, it's a number. if it aligns left, its text.) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I'm still struggling with it. I have not tried the campaign formula yet. I am still working on the date range with agent ID. The formula is working for almost all of the agents. I have 2 at the bottom that it will not bring their totals over. I have tried everything I know how to do and it will not work. I have also tried putting their ID in another cell that is calculating and it will not pull over the data. I tried changing the ID's around with other agents that are working and i get nothing. "Ron Coderre" wrote: OK....Try this: With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
Those 2 are going to the right. The others are going to the left. How can i
fix this? "Ron Coderre" wrote: Hi, Kelly This is just a guess..... See if those agents have leading or trailing spaces or..are their ID's actually numbers, where the others are "numeric text", or vice versa. (a quick test for this: click the Centering button on then off..if the value aligns right, it's a number. if it aligns left, its text.) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I'm still struggling with it. I have not tried the campaign formula yet. I am still working on the date range with agent ID. The formula is working for almost all of the agents. I have 2 at the bottom that it will not bring their totals over. I have tried everything I know how to do and it will not work. I have also tried putting their ID in another cell that is calculating and it will not pull over the data. I tried changing the ID's around with other agents that are working and i get nothing. "Ron Coderre" wrote: OK....Try this: With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
If I copy the cells above it and paste special Values it works. But when i
type their Id's back in it doesn't? "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
Try this:
Put an apostrophe in front of each (to make it text) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: Those 2 are going to the right. The others are going to the left. How can i fix this? "Ron Coderre" wrote: Hi, Kelly This is just a guess..... See if those agents have leading or trailing spaces or..are their ID's actually numbers, where the others are "numeric text", or vice versa. (a quick test for this: click the Centering button on then off..if the value aligns right, it's a number. if it aligns left, its text.) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I'm still struggling with it. I have not tried the campaign formula yet. I am still working on the date range with agent ID. The formula is working for almost all of the agents. I have 2 at the bottom that it will not bring their totals over. I have tried everything I know how to do and it will not work. I have also tried putting their ID in another cell that is calculating and it will not pull over the data. I tried changing the ID's around with other agents that are working and i get nothing. "Ron Coderre" wrote: OK....Try this: With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
Formula
YOU ARE THE BEST!!!
"Ron Coderre" wrote: Try this: Put an apostrophe in front of each (to make it text) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: Those 2 are going to the right. The others are going to the left. How can i fix this? "Ron Coderre" wrote: Hi, Kelly This is just a guess..... See if those agents have leading or trailing spaces or..are their ID's actually numbers, where the others are "numeric text", or vice versa. (a quick test for this: click the Centering button on then off..if the value aligns right, it's a number. if it aligns left, its text.) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I'm still struggling with it. I have not tried the campaign formula yet. I am still working on the date range with agent ID. The formula is working for almost all of the agents. I have 2 at the bottom that it will not bring their totals over. I have tried everything I know how to do and it will not work. I have also tried putting their ID in another cell that is calculating and it will not pull over the data. I tried changing the ID's around with other agents that are working and i get nothing. "Ron Coderre" wrote: OK....Try this: With The previous data list structure AND F1: (employee number. eg 9636) F2: (campaign prefix. eg SN) G1: (start date) H1: (end date) This formula sums the logged hours for the referenced Employee, between the referenced dates, and for the campaign prefixed by the value of F2 I1: =SUMPRODUCT(($C$2:$C$11=F1)*(LEFT($B$2:$B$11,LEN(F 2))=F2)*($A$2:$A$11=G1)*($A$2:$A$11<=H1)*D2:D11) Does that help? Anything else on the wish list? :) *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It helps a lot. As far as the campaigns is there any other way other than putting each campaign name in there to sum it? I will have 40-50 different campaigns. Here are some of the campaign names Sn_P09 Sn_P10 Sn_P11 RLG_006 RLG_007 ATCLG001 ATLCLG002 I want to add up all the SN campaigns together. All the RLG campaigns together. and all the ATL campaigns. is there a way we can add it by using just the first 2 or3 letters? "Ron Coderre" wrote: Maybe this? With A1: Date A2:?? dates B1: Campaign B2:B11 campaign codes C1: Employee C2:C11 employee numbers D1: Hours D2:D11 hours logged F1: 9636 G1: (start date) H1: (end date) This formula sums the logged hours for Employee 9836 between 9/16 and 9/17 on the SN_P09 thru SN_P11 campaign codes: I1: =SUMPRODUCT(($C$2:$C$11=F1)*($B$2:$B$11={"SN_P09", "SN_P10","SN_P11"})*($A$2:$A$11=G1)*($A$2:$A$11<= H1)*D2:D11) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: It worked thanks for your help. I have 3 other questions for you. Is there a formula that will pull the logged hours by date range? ex 9/15/2006 - 9/17/2006 = 27.5 And the other is if I have campaign names in column D. Can i have it total log hours by date by Id by campaign? if so some of the campaigns have different numbers but i want it to recognize it as one when it adds it up. for ex SN_P09, SN_P10, SN_P11, I want them to total together. "Ron Coderre" wrote: Try this: With your data list in A1:C4 E1: 9836 F1: 09/16/2006 This formula returns the total hours logged by 9836 on 09/16/2006 G1: =SUMPRODUCT(($B$1:$B$10=E1)*($A$1:$A$10=F1)*$C$1:$ C$10) Does that help? *********** Regards, Ron XL2002, WinXP "Kelly" wrote: I have a worksheet that has a lot of information in it. I am trying to find a formula that will look up logged hours based on 2 criteria. First i am looking for a specific agent that has an ID.(9876) Then i want to find a specific date (9/16/2006). I then want it to return the logged hours for that agent on that date. Ex A B C 1 9/15/2006 9836 15 2 9/16/2006 5321 16.2 3 9/16/2006 9836 12.5 4 9/17/2006 4825 13.5 I want it to give me that agent 9836 was logged in for 12.5 hours. |
| All times are GMT +1. The time now is 04:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com