Expert: Victor Lan - 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,

SUMIF is not a 3-D function, thus it cannot be used across multiple worksheets.
See http://office.microsoft.com/en-us/excel/HP101023461033.aspx for a list of Excel functions that support 3-D referencing.

It is a better solution to implement it using VBA, or create a SUMIF in each worksheet and summing them up in the totals worksheet.

Alternatively, there is a library developed by Laurent Longre -
http://xcell05.free.fr/morefunc/english/ - which contains a function THREED that can coerce a 3D range into a single array - enables array formulae with 3D references.

Using THREED, the formula to use is:
=SUMPRODUCT(THREED('SMITH:JOHNSON'!A4:A200),THREED('SMITH:JOHNSON'!B4:B200))

Please do not hesitate to contact me if you need further assistance.
Thank you.

Regards,
Victor LAN