Do I need to use INDIRECT

[haphazard]

Hey all. I am trying to get the sum of values on the same row and column of each tab in my workbook. right now i am using

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$86:$B$111&"'!$A$8:$A$78"),$A8,INDIRECT("'"&$B$86:$B$111&"'!I$8:I$78")))

but i am wondering if there is a easier way that I have just blanked on. any help would be appreciated.

 

[jimrward]

so if you had 3 sheets, called sheet1, sheet2, sheet3 and the data is in D10, you want

sheet1!D10 + sheet2!D10 + sheet3!D10

 

[T. Valko]

Hey all. I am trying to get the sum of values on the same row and column of each tab in my workbook. right now i am using

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$86:$B$111&"'!$A$8:$A$78"),$A8,INDIRECT("'"&$B$86:$B$111&"'!I$8:I$78")))

but i am wondering if there is a easier way that I have just blanked on. any help would be appreciated.

For 26 sheets, that's about as good as you can do unless you enter a SUMIF on each sheet in the same cell then get the sum of that cell across all the sheets.

You can save a couple of keystrokes:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$86:$B$111&"'!A8:A78"),$A8,INDIRECT("'"&$B$86:$B$111&"'!I8:I78")))

Since those ranges get evaluated as TEXT strings they will never change no matter where you copy the formula to so there's no need to use the $ dollar sign. In effect, they get evaulated as absolute ranges by default.

 

[Aladin Akyurek]

Hey all. I am trying to get the sum of values on the same row and column of each tab in my workbook. right now i am using

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$86:$B$111&"'!$A$8:$A$78"),$A8,INDIRECT("'"&$B$86:$B$111&"'!I$8:I$78")))

but i am wondering if there is a easier way that I have just blanked on. any help would be appreciated.

What do you mean by "easier"? Put otherwise, what is the difficulty you are observing?

 

[T. Valko]

For 26 sheets, that's about as good as you can do unless you enter a SUMIF on each sheet in the same cell then get the sum of that cell across all the sheets.

For example, let's assume you have the criteria on a sheet named Summary. In each of the other sheets enter this formula in the same cell:

=SUMIF(A8:A78,Summary!A8,I8:I78)

Let's assume you enter that formula in cell X1 of each sheet.

On your summary sheet you'd enter a formula like this:

=SUM(Sheet1:Sheet26!X1)

This is much simpler and more efficient than the SUMPRODUCT(SUMIF version. It also saves you from have to list the sheet names in a range of cells.