by c2U3MDk » Sat, 28 Feb 2009 03:24:14
2. 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.
Thank you in advance!
3. Max OR's and AND's in a WHERE clause
4. how to combine and's with or's in a if statement
6. Nesting IF's and AND's - a bit of help please...
7. IF's & AND's
8. two questions: and's inside if's and checking the return code of a program
9. New IFS interface (jfs.ifs, udf.ifs for example)
10. multiple ifs. multiple fields, multiple criteria
13. Multiple OR's in sql statment
14. multiple OR's
15. Offset & Indirect Function