sumproduct conditional statement
 

I work for a call center and I want to be able to determine the first and
second instance of a person's break.

My current euqation for lunches is:
=SUMPRODUCT(--((Data!$D$2:$D$65500)=$A3),--((Data!$E$2:$E$65500)=$B$3),--((Data!$G$2:$G$65500)=3),(Data!$H$2:$H$65500))

Where A3=agent, Col D agent lookup column, B3=date, Col E=dates, 3 represent
the lunch state found in Col G, and the time taken is in col H.

How do I re-write this part "--((Data!$G$2:$G$65500)=3)" for breaks (=7)
when there are two of them?

I will also run into issues if they split their time. I have 250 agents and
I'd like to build this for a week or two, so manual entry sounds is less than
exciting, especially when the data is 25,000 lines.

sumproduct conditional statement
 

Try

=SUMPRODUCT((Data!$D$2:$D$65500=$A3)*(Data!$E$2:$E $65500=$B$3)*(Data!$G$2:$G$65500={3,7})*(Data!$H$2 :$H$65500))

--
__________________________________
HTH

Bob

"James" wrote in message
...
I work for a call center and I want to be able to determine the first and
second instance of a person's break.

My current euqation for lunches is:
=SUMPRODUCT(--((Data!$D$2:$D$65500)=$A3),--((Data!$E$2:$E$65500)=$B$3),--((Data!$G$2:$G$65500)=3),(Data!$H$2:$H$65500))

Where A3=agent, Col D agent lookup column, B3=date, Col E=dates, 3
represent
the lunch state found in Col G, and the time taken is in col H.

How do I re-write this part "--((Data!$G$2:$G$65500)=3)" for breaks (=7)
when there are two of them?

I will also run into issues if they split their time. I have 250 agents
and
I'd like to build this for a week or two, so manual entry sounds is less
than
exciting, especially when the data is 25,000 lines.