CODEWITHSUNDEEP
excel-formulasumifs
mcv110
mcv110

Reputation: 13

Accumulate total sum based on value of cells across multiple worksheets

I am looking for a formula in excel that will calculate the sum of a given cell based on the value in a different cell, across all worksheets.

That is, I would only like the sum of a cell based on the criteria of another column, across multiple worksheets if the value matches.

Worksheet 1
19  apples
2   oranges
57  pears

Worksheet 2
2   apples
13  oranges
3   pears

Summary apples: (from all worksheets)

I would like to know how many apples I have total from every worksheet, but have not been able to properly work out the sum-if...if thats the correct tool. The sums I want to add are always in the same column across worksheets, and the criteria is always in the same column. But criteria column is never in the same order

Upvotes: 0

Views: 85

Answers (1)

mcv110
mcv110

Reputation: 13

The solution I used was this:

Input this formula into the desired Total Cell on your summary or totals worksheet

=SUM(Worksheet1:Worksheet2!<cell>)

So if in worksheet1 you have

A1  B1
19  apples
2   oranges
57  pears

And in worksheet2 you have

A1  B1
2   apples
13  oranges
3   pears

The formula that would go on your summary or totals worksheet for total apples would be as follows:

=SUM(Worksheet1:Worksheet2!A1)

Upvotes: 1

Related Questions