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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.