I need help creating a formula that looksup cell data and give a value.

BL,

Here is a solution to you specific post. I think there may be some refinements based on additional information:

First,

For this example name the two data tables (exactly as shown):

"Truck 1" and "Truck 2"

In the summary table:

B2=IF(ISBLANK(B$1),"",COUNTIF(Truck 2::$Driver, B$1)+COUNTIF(Truck 1::$Driver, B$1))

B3=IF(ISBLANK($B1),"",SUMIF(Truck 1::$Driver,B$1,Truck 1::$Gross Sales)+SUMIF(Truck 2::$Driver,B$1,Truck 2::$Gross Sales)

B4=IF(ISBLANK($B1),"",SUMIF(Truck 1::$Driver,B$1,Truck 1::$Mileage)+SUMIF(Truck 2::$Driver,B$1,Truck 2::$Mileage))

select B2 thru B4, copy, now select B2 thru the end of row 4, paste

.

For the refinements? Are there more trucks? How many Will you keep adding days to the right (you know you can only add 240 columns, right?)?

Hi br,

To show the number of days worked by a driver, you need to COUNT the number of times that driver's name appears in row 2 of both tables.

To calculate his total sales and total mileage, you need to SUM the amounts he brought in each day and the distances he drove each day.

Your functions for this are COUNTIF and SUMIF.

Formulas in At a Glance:

B2: =COUNTIF(Truck 1 :: $3:$3,B$1)+COUNTIF(Truck 2 :: 3:3,B$1)

B3: =SUMIF(Truck 1 :: $3:$3,B$1,Truck 1 :: $5:$5)+SUMIF(Truck 2 :: $3:$3,B$1,Truck 2 :: $5:$5)

B4: =SUMIF(Truck 1 :: $3:$3,B$1,Truck 1 :: $12:$12)+SUMIF(Truck 2 :: $3:$3,B$1,Truck 2 :: $12:$12)

Fill all three formulas right to the last column of At a Glance.

To put N/A in place of the 0 in Roger's column and the empty columns, wrap each of the formulas in an IF statement:

Bn: =IF(AND(ISERROR(MATCH(B$1,Truck 1 :: $3:$3,0)),ISERROR(MATCH(B$1,Truck 2 :: $3:$3,0))),"N/A",formula)

Where formula is the formula used in column B above.

Regards,

Barry

Hi br,

You'll need an instance of SUMIF (or COUNTIF) for each truck. To include N/A for names not fund, yo'll also have to include an instance of IFERROR9mATCH for each truck as well. I'd go with letting the no drives names produce a zero.

Regards,

Barry

Hi br,

"There are over 30+ trucks. Is there a way to shorten the formula?"

I think I answered this yesterday in your new thread. Here's a similar list with, I think, a few more items.

You can shorten the formulas by:

• shortening the names of the Sheets (T1, T2, T3, etc,)
• ensuring every references table has a name unique within the document (eliinates need for Sheet names in the formulas)
• shortening the names of the Tables (eg. Cpt, AaG)
• entering cell addresses directly rather than constructing them using INDIRECT
• Recording data for several trucks on the same table.
• possibly rewriting formulas to use OFFSET's capbiity to pick up values from a range of cells, and INDEX to place those values into a series of cells. (Not something I've explored, but one of the others here may have some ideas regardin tis possibiity.

The most effective way to shorten the formulas is likely the next ot last one on that list—use fewer tables.

Regards,

Barry