Expert: Jan Karel Pieterse - 11/8/2009

Question
I have 20 worksheets that contain some of the same data (names).  I want the formula to look at every worksheet, look for the name on my Totals worksheet in a specific column, then sum the data on the Totals worksheet.  Names are in the 'A' column on each sheet, data to sum is in the 'B' column on each sheet.

This is what I have that's not working.

=SUMIF(SMITH:JOHNSON!A4:A200,TotalsA3,SMITH:JOHNSON!B4:B200)

Thanks!

Ray


Answer
Hi Ray,

Unfortunately, there are not many functions that can really work accross multiple worksheets like that. SUMIF is one that can't do what you want. The only way you can achieve this with this setup in combination with SUMIF, is to add a SUMIF for each sheet separately:

=SUMIF(Sheet1!$A$2:$A$1000,"Smith",Sheet1!$B$2:$B$1000)+SUMIF(Sheet2!$A$2:$A$1000,"Smith",Sheet2!$B$2:$B$1000)+SUMIF(Sheet3!$A$2:$A$1000,"Smith",Sheet3!$B$2:$B$1000)

A much easier way to achieve this is by changing your setup. Say you have a sheet with this data for each month. Create ONE sheet and copy each month's data to this sheet. Prepend the month name in front of the entries for each month. Now you can use one of Excel's most powerful and unknown features, a pivot table, to generate a report very easily.