This is required when we need to add up the values from different sheets to a master sheet with a criteria( Name here).
F13: Need sum here of all values of "Shweta" from all sheets
Enter formula in F13:
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")))