Excel formula to search multiple sheets

Microsoft Excel for mac 2011 - macintosh
January 11, 2011 at 11:27:39
Specs: Windows Vista
I have intermediate Excel skills and am at a loss. I have a workbook that has worksheets for each month (Jan - Dec). For each worksheet (month), I tracked the donations of members for each week. I now have this workbook, and I need to create a year-end summary for the total donations for each member. Is there a way for me to create a summary page, and have it to where I can type a name and the total donation shows up? Please help!

See More: Excel formula to search multiple sheets

January 11, 2011 at 13:48:37
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:

     A        B
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:

1  Jan
2  Feb
3  Mar

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.

Have fun!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 12, 2011 at 05:50:30

Report •
Related Solutions

Ask Question