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!

=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

"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

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.

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.

1. Sumproduct assistance - need multiple ANDs and ORs

2. Need help sorting out ANDs and ORs in parameter query

3. Specify list items with unlimited ANDs and ORs?

4. Multiple Nested IFs, ORs, Ands

6. Conditional Statement with ANDs

8. multiple ifs & ands indirect/offset...need help desperately please

9. Anzac Day symbols (for Australia ands New Zealands users)

10. "ifs", "ands" & "vlookups"

12. 3 ANDS + 1 OR

13. if & ands and either indirect or offset - need help fast pleas

14. multiple ifs & ands indirect/offset...need help desperately pl

15. multiple ifs ands & indirect or offset?

4 post • Page:**1** of **1**