Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
IF statement with nested SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Conditional statement (if - then) within SUMPRODUCT | Excel Discussion (Misc queries) | |||
sumproduct and if statement | Excel Worksheet Functions | |||
SUMPRODUCT problem, using OR within statement | Excel Worksheet Functions |