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$4064DATEVALUE("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!

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)

"Ronny Hamida" wrote in message
...
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$4064DATEVALUE("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"))

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!

SUMPRODUCT with AND's and OR's?
 

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

=SUMPRODUCT(--(Sheet1!$M$2:$M$4064DATEVALUE("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" wrote:

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$4064DATEVALUE("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!

SUMPRODUCT with AND's and OR's?
 

Thank you so much, Toppers and Bob!


"Toppers" wrote:

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

=SUMPRODUCT(--(Sheet1!$M$2:$M$4064DATEVALUE("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" wrote:

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$4064DATEVALUE("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!