multiple ifs ands & indirect or offset?

multiple ifs ands & indirect or offset?

Post by c2U3MDk » Sat, 28 Feb 2009 03:24:14

I need to populate a master spreadsheet from a source file of raw data.

I need a formula that will look first for the city, then for the job code
then if both match then return the corresponding value for each of the

i.e., look for atlanta...if found then look for job code 12345...if found
then return value of inbox here; value of interview here; value of selected

is that possible? i have played with ifs & ands etc but cannot make it work.

the raw data is in a spreadsheet set up as follows:

Inbox Interview
Selected In Process Hired
City State Job Code Recruiter 361 49 22
16 10

1. Multiple If's and OR's

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


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

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

5. Or's with If's Statements

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

11. [PHP] multiple OR's

12. [PHP] multiple OR's

13. Multiple OR's in sql statment

14. multiple OR's

15. Offset & Indirect Function