Sumproduct with a vLookup, multiple criteria

[Tom F]

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

 

[Per Erik Midtrød]

If I have understood everything correctly I would have inserted the
store name in column L (L2=A2) on Sheet2,and a third column in Sheet3
with a vlookup to return which store each numeric job code represents.
Then your formula would be:
SUMPRODUCT((Sheet2!A$2:$A14=Sheet1!A7)*(Sheet2!G$2:G$14="R")*(Sheet3!C$2:C$14=Sheet1!A7)*(Sheet3!B2:B14))

Per Erik

 

[Tom F]

Per, thanks for responding.
Actually, the numeric job code represents the employee, not the store.
For example, at Walmart you might find that a cashier is given the job
code "Cash", but each cashier may be paid differently on a scale. The
numeric number would indicate what pay level each "Cash" was. "Cash" at
job level 29 = $20,000, and "Cash" at level 31 = $23,000.

I'm really trying to do this to learn these functions, and to avoid
cutting and pasting any columns. I'd like to build a function that
would do all the referencing for me.

Thanks for your input. I appreciate it!!

 

[Tom F]

Can anyone help me with this? Thanks!