VLOOKUP?

[smooney]

I need to know if this is possible.

I have a workbook with several worksheets. Each worksheet looks like
(example):

Worksheet #1
Column A Column B Column C
Active Smith, Jane 10
Inactive Doe, John 12
Leave Brown, Lee 30

Worksheet #2
Column A Column B Column C
Leave Doe, John 5
Leave Brown, Lee 15
Active Smith, Jane 40

What I need is the sum of all entries in column C from all worksheets (by
row). And I need these numbers to stay with the person. The problem is that
the person's name is not in the same row on every worksheet.

So the 'totals' worksheet would look like this:

Column A Column B
Smith, Jane 50
Brown, Lee 45
Doe, John 17

I hope this makes sense.
Thanks

 

[Pete_UK]

How many is "several" worksheets? How many rows in each of these
sheets?

Assuming you have 3 sheets plus the totals sheet, here is one way of
doing it - enter this in B2 of the totals sheet:

=IF(ISNA(VLOOKUP(A2,Sheet1!B$2:C$100,2,0)), 0,VLOOKUP(A2,Sheet1!B$2:C
$100,2,0)) + IF(ISNA(VLOOKUP(A2,Sheet2!B$2:C$100,2,0)),
0,VLOOKUP(A2,Sheet2!B$2:C$100,2,0)) + IF(ISNA(VLOOKUP(A2,Sheet3!B$2:C
$100,2,0)), 0,VLOOKUP(A2,Sheet3!B$2:C$100,2,0))

All one formula - be wary of spurious line-breaks.

I've assumed that you have data up to row 100 in each sheet, so change
this if you have more. Hopefully you can see how to extend it if you
have more sheets - just add:

+if(isna(vlookup(...Sheet4...)),0,vlookup(...Sheet4...))

to the end of the formula for a 4th sheet, and so on.

Copy the formula down column B of the totals sheet, as required.

Hope this helps.

Pete

 

[Teethless mama]

=SUM(SUMIF(INDIRECT("Sheet"&{1,2}&"!B1:B100"),A1,INDIRECT("Sheet"&{1,2}&"!C1:C100")))