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

#### MackyD

##### New Member
=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>

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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))

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

Good! Thanks for the feedback.

Replies
5
Views
774
Replies
3
Views
1K
Replies
3
Views
457
Replies
0
Views
522
Replies
3
Views
1K

1,196,483
Messages
6,015,457
Members
441,897
Latest member
erma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back