Hi All, first time poster:

I'm trying to build a formula that will return a sum of several
products, while using several criteria. This is much more in depth than
I've had to ever get before. let me try to explain what I'm doing:

On Sheet 1, I have a list of stores, and on Sheet 2 I have a list of
employees at each store. Each employee has a two job codes listed next
to their name that determines that employee's sales goal for the year.
On Sheet 3 is a table that lists the job codes (column A) and the sales
goal for that job code (column B).

So, what I want the formula to do is look on sheet 2 and identify the
employees that work in the store. I've gotten it to do a count of those
employees with this formula:

=SUMPRODUCT(('Sheet 2'!$A$2:$A$100='Sheet 1'!A7)*('Sheet
2'!$G$2:$G$100="R"))

The "R" is solely an identifier that tells me that they have a sales
goal. This is the "unimportant" job code. In the next column is a
second job code ("important") that is numeric. That numeric code
corresponds to the table on Sheet 3.

Ok, if I'm still making sense, let me finish this thought. The formula
would need to look for the Store Name, the Unimportant Job Code and the
Important Job Code on Sheet 2. once it has that information, it has to
find the goal for each employee on Sheet 3. I then want it to give me a
sum of the goals for all the employees that it found for that store.

Notes:
Sheet 1, Column A = Store Name
Sheet 2, Column A = Store Name
Sheet2, Column G = Alphabetic job code (i.e. "R")
Sheet 2, Column L = Numeric job code
Sheet3, Column A = Numeric job code
Sheet3, Column B = Annual Sales goal by job code (i.e. $100,000)

Any help or questions are definitely appreciated!!!

Tom