Search This Blog

Friday 10 August 2012

ADD VALUES FROM DIFFERENT SHEETS OF A WORKBOOK WITH A CRITERIA

This is required when we need to add up the values from different sheets to a master sheet with a criteria( Name here).

Example:
Sheet1:
D6: Shweta
E6: 200

Sheet2:
A2: Shweta
B2: 300

Sheet3:
G10: Shweta
H10: 500

Sheet4:
E13: Shweta
F13: Need sum here of all values of "Shweta" from all sheets

Enter formula in F13:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!A:G"),E13,INDIRECT("Sheet"&{1,2,3} &"!B:H")))

Now the question arises what if I have a large number of sheets in my workbook. Say, I need to maintain a different sheet for each day of the month to track the transactions processed by the associates. Hence there are 31 sheets for 31 days of the month.Then it would not be possible to write the sheet number 1,2,3,....31 in the above formula. 

Here is a solution:

Write all the sheet names in a column and give a named range to them. Say MySheets and use below array formula

=SUM(SUMIF(INDIRECT(MySheets & "!A:G"),E13,INDIRECT(MySheets & "!B:H")))
with CSE




No comments:

Post a Comment