Sum Sheets

[brooklyn2007]

Hello. I have many sheets (each sheet representing a month) in my workbook with a list of client names and values next to each name. The names of clients are in different order per each sheet. Anyway the clients are the same per each sheet. I need to find a formula to sum of the values per each client. I have tried SUMIF by pressing SHIFT and holding it down to select the entire sheets and after that I have selected the range and criteria and so on, but it won't work. Please help

 

[Aladin Akyurek]

Hello. I have many sheets (each sheet representing a month) in my workbook with a list of client names and values next to each name. The names of clients are in different order per each sheet. Anyway the clients are the same per each sheet. I need to find a formula to sum of the values per each client. I have tried SUMIF by pressing SHIFT and holding it down to select the entire sheets and after that I have selected the range and criteria and so on, but it won't work. Please help

Create a range housing the names of the relevant sheets. Name this range as SheetList.

Let A2:A100 house clients in every sheet and B2:B100 the corresponding values...

The destination sheet...

E2 houses a name of interest.

F2:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A100"),E2,INDIRECT("'"&SheetList&"'!B2:B100")))

 

[brooklyn2007]

Create a range housing the names of the relevant sheets. Name this range as SheetList.

Let A2:A100 house clients in every sheet and B2:B100 the corresponding values...

The destination sheet...

E2 houses a name of interest.

F2:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A2:A100"),E2,INDIRECT("'"&SheetList&"'!B2:B100")))

Nope

Excel Workbook
	A	B	C	D	E	F
1
2
3
4
5
6
7			john	#REF!
8			jim
9			ken
10
11
12
Sheet1

Excel Workbook
	A	B	C	D	E	F
1
2
3
4
5
6
7
8			john	23
9			jim	41
10			ken	26
11
12
13
14
Sheet2

 

[Aladin Akyurek]

Did you create SheetList?

 

[brooklyn2007]

Did you create SheetList?

Yes, I named it Sheetlist

 

[Aladin Akyurek]

Yes, I named it Sheetlist

Care to post the range housing the sheet names?

 

[brooklyn2007]

Care to post the range housing the sheet names?

Just fixed the problem, thanks it work

 

[Aladin Akyurek]

Just fixed the problem, thanks it work

Great. Thanks for the feedback.