Excel 2013 formula to extract totals from specific rows on other worksheets by c

Question

Excel 2013 formula to extract totals from specific rows on other worksheets by column name.

Hi,

I have a workbook I complete monthly for a construction company. It's direct payroll that totals number of hours worked per house . The column titles are the house addresses; the hours worked are entered down each column. The number of worksheets in the workbook may vary from month to month but are set up the same with the exception of houses (completed houses will be omitted and new houses will be added). Each worksheet title is the payroll period (ex. 07-01 to 07-14)

I'm looking for a formula that will add rows 56 and 106 (not through 106) for each house (column title), in each worksheet and display the totals on a summary worksheet. The idea is to show total $ of payroll per house for the entire month.

I've tried vlookup but cannot figure out how to tell excel to use the per house title.

I can't attach the workbook for privacy reasons. It contains names and Social Security numbers.

Your help and/or suggestions would be very appreciated.

Explanation / Answer

You will probably be better off with an INDEX(matrix,Row#,Col#) type of formula instead of a VLOOKUP()

Let's say your house titles are in row 1 of a sheet named Houses.

Now on the sheet where you want to bring over the amounts for a given house, say the Pink House

you might have Pink House in cell A2 of your summary sheet.

Then you could have a formula like this, in another column on the summary sheet, row 2:

=INDEX(Houses!$A$1:$DZ$56,56,MATCH(A2,Houses!$1:$1,0)) +INDEX(Houses!$A$1:$DZ$106,106,MATCH(A2,Houses!$1:$1,0))

The $A$1:$DZ$56 and $A$1:$DZ$106 refers to all the cells over on the Houses sheet with information in them. We will only look at rows 56 and 106 in that area, so we don't have to reference anything below 106.

the MATCH(A2,Houses!$1:$1,0) is going to look in row 1 of the Houses sheet for an exact match to whatever is in A2 on the summary sheet and it returns a column number. So that works to tell Excel what column to look in, and we already told it row 56 in the first formula, and row 106 in the second one, so it should give you the total for the Pink House. The formula should fill down your summary sheet very nicely so you can also total up the Brick House, the Stick House, the Straw House and even the Outhouse, if necessary.

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.