SUMPRODUCT using OR with two or more text strings

**bkatzman** · 03-31-2010, 03:53 PM

=SUMPRODUCT(--(C:C="Jack")--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2005,12,31)))

How can I modify this to look for Jack, or Hurley, or John, or Sawyer?

Thanks a lot!
Bryce

**zbor** · 03-31-2010, 04:06 PM

Instead of "John" write some cell reference so you can change value of cell. For example D1:

=SUMPRODUCT(--(C:C=D1),--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2005,12,31)))

**bkatzman** · 03-31-2010, 04:10 PM

I'm not sure this would work for us. I want to use this function as part of a template that I can dump a lot of data (about 1k rows) that have differing dates in A,C

**zbor** · 03-31-2010, 04:12 PM

Uplaod example workbook. Maybe a Pivot table is what you need.

**DonkeyOte** · 03-31-2010, 04:54 PM

Originally Posted by bkatzman

How can I modify this to look for Jack, or Hurley, or John, or Sawyer?

OR's are generally conducted via Addition in Arrays/Sumproducts, ie:

=SUMPRODUCT((C:C="Jack")+(C:C="Sawyer")+(C:C="Hurley"),--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2005,12,31)))

However, for multiple search terms it's often easiest to use an ISNUMBER based test:

=SUMPRODUCT(--ISNUMBER(SEARCH(C:C,{"Jack","Sawyer","Hurley"})),--(A:A>=DATE(2005,10,1)),--(A:A<=DATE(2005,12,31)))

Try to avoid using entire column references though... (pre XL2007 you can't)