=IF(D10>0,</SPAN>SUMPRODUCT(--(Workflow!A:A=Apr13!E10</SPAN>),--(Workflow!E:E=List!C2</SPAN>))</SPAN>,"0")
In the above the first array</SPAN> is looking up the assigned date in E10 in my workflow tab. The second array</SPAN> is looking for files "Closed" on that date.
I want the change my Workflow! column C to have two options "First Closed" and "Comeback Closed". However I’ve not been able to edit the above to count either option that also meat the date criteria. Presently I use a separate a column for the Fist and Comeback section so I can just list "closed" to both. (hope that makes sense!)
I understand that "+" in a sumproduct should act as an "or" function but I haven’t got this to work yet! And the only examples I can find of this don’t include multiple criteria or text. I have also tryed to use *closed* as an alternative method of defining the count but also failed!
The below kind of demonstrated what I’m trying to achieve although it’s obviously wrong;
SUMPRODUCT(--(Workflow!A:A=Apr13!E10</SPAN>),(</SPAN>or(</SPAN>--(Workflow!E:E=List!C2</SPAN>),(--(</SPAN>Workflow!E:E=ListC3</SPAN>)))
Any suggestions? or diffrent fuctions that would work better?</SPAN></SPAN>
In the above the first array</SPAN> is looking up the assigned date in E10 in my workflow tab. The second array</SPAN> is looking for files "Closed" on that date.
I want the change my Workflow! column C to have two options "First Closed" and "Comeback Closed". However I’ve not been able to edit the above to count either option that also meat the date criteria. Presently I use a separate a column for the Fist and Comeback section so I can just list "closed" to both. (hope that makes sense!)
I understand that "+" in a sumproduct should act as an "or" function but I haven’t got this to work yet! And the only examples I can find of this don’t include multiple criteria or text. I have also tryed to use *closed* as an alternative method of defining the count but also failed!
The below kind of demonstrated what I’m trying to achieve although it’s obviously wrong;
SUMPRODUCT(--(Workflow!A:A=Apr13!E10</SPAN>),(</SPAN>or(</SPAN>--(Workflow!E:E=List!C2</SPAN>),(--(</SPAN>Workflow!E:E=ListC3</SPAN>)))
Any suggestions? or diffrent fuctions that would work better?</SPAN></SPAN>