Sum from different sheets

[cflorackis]

Dear all,

I am trying to do a summation using information from different sheets.

I essentially have information on the names and compensation of the employees of company A----data for different years come in different sheets-----e.g. here is an example of how my data are structured for year 2007

sample.xls
	A	B	C	D
1	Company A: Year 2007
2	Name	Role	salary	bonus
3	george	FD	10	7
4	John	CEO	15	8
5		ED Total	23	9
6		ED Average	47	5
7	jim	Independent NED	7	8
8	Steven	Independent NED	3	4
2007

and here for year 2006:

sample.xls
	A	B	C	D
1	Company A: Year 2006
2	Name	Role	salary	bonus
3	george	FD	8	30
4	John	CEO	5	20
5		ED Total	2	12
6		ED Average	4	7
7	jim	Independent NED	3	4
8	Bill	Independent NED	6	11
2006

What I need to do is to find a way to calculate the sum of salary and bonus for company A for each year separately and then have it in a different sheet or file e.g. according to the information provided above the sum of salary for year 2006 is 22 (8+5+3+6)----please note that the numbers in cells A5 and A6 have to be ignored since they do not correspond to any individuals.

Similarly, for year 2007, the sum of salary is 35 (10+15+7+3)

Here is an example of how i would like to have my data organized

Book1
	A	B	C	D	E
1	Company	Year	sum of salary	sum of bonus	number of indiviuals paid
2	companyA	2007	35	27	4
3	companyA	2006	22	65	4
Sheet1

(Column E just reports the number of individuals paid in each year)

many thanks in advance

This has been causing problems to me for a long time so any help will be greatly appreciated

C.

 

[Stormseed]

is this what you want ?

Look for the formula in the third table:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		CompanyA2007						CompanyA2006
2	Name	Role	Salary	Bonus			Name	Role	Salary	Bonus
3	George	FD	10	7			George	FD	8	30
4	John	CEO	15	8			John	CEO	5	20
5			23	9					2	12
6			47	5					4	7
7	Jim	Independent	7	8			Jim	Independent	3	4
8	Steven	Independent	3	4			Steven	Independent	6	11
9
10
11		Company	year	sumsalary	sumbonus	numberofindividualspaid
12		CompanyA	2007	35	27	4
13		CompanyA	2006	22	65	4
14
Sheet1

 

[Stormseed]

In cell D12 - the formula will be:
=SUMPRODUCT(--(A3:A8<>""),(C3:C8))

In cell D13 - the formula will be:
=SUMPRODUCT(--(G3:G8<>""),(I3:I8))

In cell E12 - the formula will be:
=SUMPRODUCT(--(A3:A8<>""),(D3:D8))

In cell E13 - the formula will be:
=SUMPRODUCT(--(G3:G8<>""),(J3:J8))

In cell F12 - the formula will be:
=SUMPRODUCT(--(A3:A8<>""))

In cell F13 - the formula will be:
=SUMPRODUCT(--(G3:G8<>""))

 

[Aladin Akyurek]

...

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A3:A8"),"<>",INDIRECT("'"&SheetList&"'!C3:C8")))

where SheetList refers to a range housing the names of the relevant sheets.

 

[cflorackis]

Many thanks to both of you

Stormseed’s solution, though, does not help much since it requires to copy and paste all my data to the same sheet, which is a very difficult thing to do for the case of my sample.

Regarding Aladin’s suggestion: Aladin, may ask for some clarifications :

1) I tried it but its seems that the formula does not take into account the blank cells---e.g. remember that when there is a blank cell in column A, the corresponding salary values have to be ignored

2) I am using the formula to get the sum of salary (column C) . Is there a way to use the Autofill option to get the sum of bonus (column D)---I tried to drag on the right but it does not work

Many thanks again for your valuable help

C.

 

[Stormseed]

Stormseed’s solution, though, does not help much since it requires to copy and paste all my data to the same sheet, which is a very difficult thing to do for the case of my sample

Dude, try and think out of the box. You should apply the same logic at your end on different sheets. I thought what you needed is the idea to calculate the sum of salary and bonus for individuals who had a name in column A. I did not post a screenshot of the above example on different sheets because I was short of time.

you are welcome.

 

[Aladin Akyurek]

Many thanks to both of you

Stormseed’s solution, though, does not help much since it requires to copy and paste all my data to the same sheet, which is a very difficult thing to do for the case of my sample.

Regarding Aladin’s suggestion: Aladin, may ask for some clarifications :

1) I tried it but its seems that the formula does not take into account the blank cells---e.g. remember that when there is a blank cell in column A, the corresponding salary values have to be ignored

2) I am using the formula to get the sum of salary (column C) . Is there a way to use the Autofill option to get the sum of bonus (column D)---I tried to drag on the right but it does not work

Many thanks again for your valuable help

C.

The formula won't sum numbers associated with empty cells. It does, however, sum all the years together. That is, it does not sum by companies.

May I ask whether the sheet named 2006 contain different companies?

 

[cflorackis]

Aladin,

the sheet named 2006 refers to the same company as the sheet named 2007. The two sheets may contain different individuals

cheers

C.

 

[Aladin Akyurek]

Sheet1...

C2, coy down:

=SUMIF(INDIRECT("'"&$B2&"'!A3:A8"),"<>",INDIRECT("'"&$B2&"'!C3:C8"))

D2, copy down:

=SUMIF(INDIRECT("'"&$B2&"'!A3:A8"),"<>",INDIRECT("'"&$B2&"'!D3:D8"))

E2, copy down:

=COUNTIF(INDIRECT("'"&$B2&"'!A3:A8"),"?*")

P.S. You can avoid calling INDIRECT if you calculate the results locally on the data sheets and collect the results on Sheet1.

Aladin,

the sheet named 2006 refers to the same company as the sheet named 2007. The two sheets may contain different individuals

cheers

C.