Sum across sheets using row and column criteria

**urisimba** · 03-09-2010, 06:13 PM

hello all.

i have a spreadsheet with 12 diffrent databases on 12 diffrent sheets. every database is set as a table in which the columns headers are names and at the right of every row sits a row critera. for example:

james brian david
5 10 6 days of work
2 3 8 clients
19 22 35 total hours of work

every sheet has this table but for a diffrent month. and so, for every sheet the table is similar but the numbers are diffrent.

i need to sum across sheets every time a cell is located in the cross "brian" and "total hours of work".

i should also state that the location of "brian" isn't exactly the same in every sheet. each sheet "brian" is located in a diffrent column. so i can not do a simple sum across sheets but i have to do a criteria based sum.

any idea will be appriciated.

thanks in advance

**teylyn** · 03-09-2010, 07:33 PM

Urisimba, welcome to the forum.

I've moved your post to a question forum. Please take care to select the appropriate forum before you ask your next question.

thanks

**jnewby72** · 03-09-2010, 08:56 PM

Hello urisimba,

I've put together a little demo workbook that might offer a solution. In the workbook, I have set up a sheet for January and February with a table of data on each. Each table contains a person's name who isn't in both tables.

Anyway, I use the HLOOKUP function to get the desired value from each table. The retrieved values are summed after being checked for an error. This requires an array formula. Anyway, have a look and see if it has potential.

**urisimba** · 03-10-2010, 02:40 AM

thanx a lot!

i have to go to work now so i will check it out later. i will let you know how it went.

**DonkeyOte** · 03-10-2010, 03:11 AM

Conditional Summation in 3D is non-trivial

For an overview of the basic options open to you see John McGimspey's page: http://www.mcgimpsey.com/excel/threedsumif.html

Personally I favour conducting the calcs on each sheet and using 3D Sum.
However, pending volume of data in each table and number of calcs being performed a Volatile SUMPRODUCT might not prove too oppressive in terms of performance.

(A Multi Consolidation Pivot might also work).