How to add the values on different sheets of a workbook

[Shweta]

Hi All,

I am facing the problem with sum function.
I have the data on different sheets of a workbook. On sheet1
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl69 id=td_post_2835456 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>C16</TD><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>D16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>shweta</TD><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">45</TD></TR></TBODY></TABLE>

sheet2

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>E4</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>F4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>shweta</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">50</TD></TR></TBODY></TABLE>

sheet3

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>C3</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>D3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>shweta</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">47</TD></TR></TBODY></TABLE>

Sheet4

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A1</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=64>B1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>shweta</TD><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">527</TD></TR></TBODY></TABLE>

On sheet5 I need to add all the values of Shweta. If I am using the sum function I need to select the cells one by one. I have 65 names like this. so it is not being possible to add the values one by one for each person.

Please help me out.

Thanks
Shweta

 

[jbeaucaire]

Like so:

Excel Workbook
	A	B	C
1
2		shweta	45
3		doug	33
4
Sheet1

Excel Workbook
	D	E	F	G
3
4		shweta	50
5
6		doug	25
7
Sheet2

Excel Workbook
	A	B	C	D	E
1
2		doug	22
3			shweta	47
4
Sheet3

Excel Workbook
	A	B	C	D
1	shweta	527
2
3			doug	33
Sheet4

Excel Workbook
	A	B	C	D
1
2	shweta	669
3	doug	80
4
5
Sheet5

 

[Shweta]

Thanks Jbeaucaire!

But can you please tell me why you took "!A:G" & "!B:H".

Shweta

 

[jbeaucaire]

SUMIF() 101...

When using SUMIF you need two identical ranges. The first range is the evaluation range that you are searching for specific value(s). I simply selected range A:G as the cells to look for the names in (you can expand/contract that as you wish).

The second range is the range to sum numbers from. Based on your example, the numbers to sum are in the immediate cell to right of the found names, so I summed from an identically sized range offset by 1 column from the original range, B:H. Again, you can adjust that as needed.

 

[Shweta]

Anyways your formula is working fine.

Thanks a lot!

Regards,
Shweta

 

[Shweta]

Ok, I understood.

So if I don't know where is the data in that case I can select the whole range of a sheet.

Shweta

 

[jbeaucaire]

For efficiency's sake, I hope you DON'T do that. You should select a reasonable range of columns, not 100s and 1000s.

 

[Shweta]

One more question Jbeaucaire....

I have 31 sheets for 31 days of a month. So in that case do I need to enter the sheet no. manually in the formula like "sheet"&{1,2,3,4....till 31} or I can just use the range as "sheet"&{1:31}.

Please tell me the shortcut if any....

 

[jbeaucaire]

Trial and error would probably show you can't use that shortcut.

There is another way. Let's say you want these formulas to expand in an easy way. For that I would us a "list" of sheet names and a Named Range to evaluate that list.

In this example, I'm using column F to list sheets as I add them. I only put sheet names here that actually exist. Then I used a dynamic formula to expand that list into a named range called MySheets by using this.... Insert > Name > Define

=OFFSET(Last!$F$1,,,COUNTA(Last!$F:$F),)

With that, I can use the ARRAY formula shown below, confirmed by pressing CTRL-SHIFT-ENTER to activate the array, then copied down.

Excel Workbook
	A	B	C	D	E	F
1						Sheet1
2	shweta	60				Sheet2
3	doug	20				Sheet3
4						Sheet4
5
Summary Sheet

 

[Shweta]

Thanks a lot Jbeaucaire!