Search code examples
excelreferenceworksheet

Sum the total of a column in another worksheet

I am looking for a nice formula that will sum the total of a column in a worksheet indirectly.

I have Worksheet 1 with two columns

A             B
Worksheet1
Worksheet2
workSheet3

I need to fill B with the value of the SUm of column C in the corresponding worksheets in column A

So if the sum of Column C in Worksheet1 was 15, I would expect 15 to show in Column B next to worksheet1.

My attempt so far has failed using =SUM(INDIRECT(A54,TRUE)!C:C)

Solution

  • Well, that won't work because INDIRECT takes only one parameter. Try this instead:

    =SUM(INDIRECT("'"&A54&"'!C:C"))

    The & concatenates the value of A54 with that of !C:C, which, for the first worksheet is: Worksheet1!C:C which is the syntax for referencing from other sheets. :)

    EDIT: I added single quotes because if you have, for example a worksheet name with a space (e.g. Worksheet 1, the function will give you an error). The single quote will regroup the whole worksheet name together and prevents excel from getting confused.