## SUMPRODUCT with AND's and OR's?

### SUMPRODUCT with AND's and OR's?

Hello there!

I have a worksheet that I need to perform a count for every time something
meets certain criteria that I'm looking for. Normally, I would use something
like SUMPRODUCT(--(array1), --(array2), --(array3), . . .) but this only
seems to work if each array is a simple formula AND if each array looks in a
different column each time. (In other words, I cannot seem to do a "greater
than 'x' in column M" AND a "less than 'x' in column M" within the same
SUMPRODUCT function. Here's what I have (and please note that I will
eventually use dynamic headers once I know everything is working):

=SUMPRODUCT(--(Sheet1!\$M\$2:\$M\$4064>DATEVALUE("1/1/2006"),--(Sheet1!\$M\$2:\$M\$4064<DATEVALUE("3/31/2006"),--(LEFT(Sheet1!\$R\$2:\$R\$4064,12)<>"SUPERMAN"),--(LEFT(Sheet1!\$R\$2:\$R\$4064,12)<>"MIGHTYMO"))

What I would like it to do is to give a count of each record/line/row that
meets the following criteria:

- Has a date between 1/1/2006 and 3/31/2006 within column "M"; and
- does not have "SUPERMAN" or "MIGHTYMO" in the 12 left-most characters
of column "R"

Is there any other way or formula that can do this? I may need to add more
"arrays" to this formula as well, so the easability of the SUMPRODUCT was
what I tried to use, but it looks like it won't give me the correct
information.

### SUMPRODUCT with AND's and OR's?

=SUMPRODUCT(--(Sheet1!\$M\$2:\$M\$4064>--"2006-01-01"),--(Sheet1!\$M\$2:\$M\$4064<--
"2006-03-31"),
--(LEFT(Sheet1!\$R\$2:\$R\$4064,8)<>"SUPERMAN"),--(LEFT(Sheet1!\$R\$2:\$R\$4064,8)<>
"MIGHTYMO"))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

something
a
"greater
=SUMPRODUCT(--(Sheet1!\$M\$2:\$M\$4064>DATEVALUE("1/1/2006"),--(Sheet1!\$M\$2:\$M\$4
064<DATEVALUE("3/31/2006"),--(LEFT(Sheet1!\$R\$2:\$R\$4064,12)<>"SUPERMAN"),--(L
EFT(Sheet1!\$R\$2:\$R\$4064,12)<>"MIGHTYMO"))
characters
more

### SUMPRODUCT with AND's and OR's?

Ronny,
There were a couple of brackets missing in your formula:

=SUMPRODUCT(--(Sheet1!\$M\$2:\$M\$4064>DATEVALUE("1/1/2006")),--(Sheet1!\$M\$2:\$M\$4064<DATEVALUE("3/31/2006")),--(LEFT(Sheet1!\$R\$2:\$R\$4064,12)<>"SUPERMAN"),--(LEFT(Sheet1!\$R\$2:\$R\$4064,12)<>"MIGHTYMO"))

Otherwise it worked OK for me.

SUMPRODUCT does handle the "Between dates" situation.

### SUMPRODUCT with AND's and OR's?

Thank you so much, Toppers and Bob!