How can i include an or option in my sumproduct counting code?

[MackyD]

=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>

 

[pgc01]

Hi

Assuming the value in List!C2 is different from the value in List!C3, for ex.:

=SUMPRODUCT(--(Workflow!A:A='Apr13'!E10),(Workflow!E:E=List!C2)+(Workflow!E:E=List!C3))

Remark:
It's not efficient to use whole column references. It's more efficient to use just a specific range, where you know the values are.
For ex., assuming you know that the values will always in the first 1000 rows, use instead:

=SUMPRODUCT(--(Workflow!A1:A1000='Apr13'!E10),(Workflow!E1:E1000=List!C2)+(Workflow!E1:E1000=List!C3))

 

[MackyD]

I had thought i'd tried this but must have made an error before as it is working now cheers

 

[pgc01]

Good! Thanks for the feedback.