SUMPRODUCT with AND's and OR's?

[Ronny Hamida]

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.

Thank you in advance!

 

[Bob Phillips]

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

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$4

 

[Toppers]

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.

 

[Ronny Hamida]

Thank you so much, Toppers and Bob!

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.