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(--(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!

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!

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!