|Since we can't see your spreadsheet from where we're sitting, we can't give you an exact solution.|
Perhaps these concepts can be adapted to fit your needs.
You could use a SUMIF function on each sheet and then SUM all of those cells together on your summary sheet, but that's boring.
Let's say I have 4 sheets named Jan, Feb, Mar and Summary.
In the Summary sheet I will use B1 as the cell to contain the name of the person I am interested in. (BTW I would use a Drop Down list of names so I wouldn't have to type anything in)
Let's say I have data that looks like this on each of the 3 monthly sheets:
1 Tom 2
2 Fred 3
3 Tom 2
4 Fred 3
In each sheet I could go the boring route and use this formula in, say, A6:
=SUMIF(A1:A4, Summary!B1, B1:B4)
Then in the Summary sheet I could use:
That would SUM each SUMIF result and give me the total.
A much more elegant way is to do everything on the Summary sheet as follows:
In Summary!A1:A3 (or some out of the way place) I would put a list of my monthly sheet names:
Still using Summary!B1 as the cell to contain the name I am searching for, I would put this in Summary!C1:
=SUMPRODUCT(SUMIF(INDIRECT("'" & $A$1:$A$3 & "'!A1:A4"), B1,
INDIRECT("'" & $A$1:$A$3 & "'!B1:B4")))
Make sure you note the difference between the double quotes and the single quotes or this formuala won't work.
After each INDIRECT you should have Open Parenthesis, Double Quote, Single Quote, Double Quote.
Before each Exclamation Point, you should have Double Quote, Single Quote
You should study up on the SUMPRODUCT, SUMIF and INDIRECT functions to help you understand how this works. By understanding each of the 3 functions, you should be able to adapt the concept to fit your needs.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.