tough formula I can't figure out...

For the formulas in the example below, I've renamed the Table on Sheet 5 to call it Customers.

And the four "Week 1" tables are renamed "Alice", "Bob", "Carol" and "Dan"

If you give similar names to the tables in your document, Numbers will automatically adjust any existing formulas to match the new names.

For the number of people who worked on each job, I made the following assumptions:

Each person who worked on a job did so only on one occasion per job.

Each customer had only one job listed on the Customers table.

Here is a model of the five Tables involved in these calculations. I've entered data only in the cells that are involved in the calculations in the formulas shown below.

The Customers table, at top right, contains no formulas used in his example. The data in columns A and B is copied from your equivalent table, with the customer names shortened to fit this space more conveniently.

Each of the four 'employee' tables has the same three formulas:

• Your XLOOKUP formula, entered in G2, and filled down, which retrieves the quoted hours for each customer names in column A from column B of the Customers table:

The formula below, placed in H2 of each table, then filled down to the bottom of that column.

This copy of the formula was placed in Alice's table, so that table's name is missing from the first COUNTIF statement. If you copy this formula and paste it into each of the other tables, you will need to re-insert 'Alice's table name in the first COUNTIF. Note that if all of these Tables keep the default "Table 1" name, you will need to add the Sheet name before Table 1 in each case.

The formula counts the number of times the customer name in 'this row' appears in the four tables.

The formula below in placed in cell I2 (eye-too) of each of the four tables, then filled down the column.

Note that because this calculation involves only cells on the table that it is in, there is no need to include the Sheet name or Table name for each cell.

Regards,

Barry

Observation: Apparently I hadn't finished placing the formulas in Alice's table before taking the screen shot. Oops!

"I can't have any additional columns on sheets 1-4 (Alice and the gang) I need colum G to display the original job number or if there is multiples it needs to display the adjusted job hours."

Was this information included in the original question? Most people responding here, including me, base their replies and sollutions on the information supplied by the person reporting the problem.

"adding the additional columns to this sheet unfortunate defeats the purpose of the project. "

See above. I don't recall seeing a 'purpose statement' that would preclude these changes being introduced. Was there one?

For someone at this end of the conversation, there are several purposes, some stated, others unstated.

One is to solve the issue, if possible.

Another is to ask for clarification of the issue, and of the expected results (not necessarily in those words).

A third is to give the OP (original poster of the question)information that will help her/him understand what's going on 'behind the curtain' in a way that could be useful to her/him in setting up similar documents in the future.

Separating the parts of the process can be useful in reaching the last-mentioned purpose.

Take a look at the revised tables below. I've reduced the names of customers to fit the tables into a single screen shot, and not filled in any data not related to the issue stated in the question. Ignoring those missing items, does what is shown fit your purpose?

Regards,

Barry