Sum values across multiple spreadsheets

[Admiral_Charles]

Generic example of what I want to do:[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]6640[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9360[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2712[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3214[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]9512[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]8439[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
When B1:B6 = "X", sum cell H10 from all tabs where value A1 = cell B8 on all tabs.

So for this example, Sheet2 has cell value of B8 = 6640, so return cell H10 from that sheet
Sheet6 has cell value of B8 = 9512, so return cell H10 from that sheet and add it to previous.

And so on for all columns...

I would like to do all this in one formula without VBA or Macros.

 

[schwiggiddy]

Hi Adm. Charles,

I'm not sure exactly what you're asking for, but I'll offer this tip that maybe can get you going in the right direction: change your Xs to 1s. With 1s you can employ a multiplier so a function returns only the values related to 6640 on you sheets 1 and 2 but nothing from sheets 4-6. Then you'd sum them. So, if I'm understanding, your sum cell would be some variant of B1 * Sheet1!H10 + B2 * Sheet1!H10 +...

I did something like this, I think, with a similar looking matrix years ago. Changing my predecessor's Xs to 1s made a world of difference.