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

folders...

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

here...etc.

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

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

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