Conditional Calculation On Many Worksheets

**Dave Preston** · 11-11-2007, 05:32 AM

Firtsly , apologies for the Title but it's hard to define the problem:-

I need to create a shelf summary worksheet that calulates the number of shelves of type, width and length from many worksheets representing shop fittings. The end result is a matrix:-

Shelf Depth
Shelf Type / Length 450 600 750
============================
Type A Short
Type A Medium
Type A Long
Type B Short
Type B Medium
Type B Long
Type C Short
Type C Medium
Type C Long

Ther are 3 types shelf types, 3 lengths and 10 depths.

This is calculated from many worksheets. Each of these worksheets contain a cell that defines the type (A, B or C), a cell for each side of the fitting that contains the width (450, 750 etc). Below each side is information about each shelf including it's length (S, M or L).

I add the worksheets to the spreadshet programatically, I don't know how many there will be but I know they will all be added in sequence from a template worksheet and they will begin with IG or WG.

I have already managed to do a sum on the cells "=SUM(IG 1!C20:WG Blank!C20)" (Not sure of syntax as doing from memory at home) which works. What I can't do is to add the conditional statement that filters the type and depth from the other cells. I tried referring to them using "=IF(IG 1!C20,"=450":WG Blank!C20,"=450") but it causes an error.

Is it possible?

I can quite easily do this using VBA, but the user may need to manually change the shelf depths and I don't want them to have to initiate anything to recalculate the totals. I could trigger it with an event but it would be overkill and besides which if Excel can do it, why write a program.

**EdMac** · 11-12-2007, 05:43 AM

Hi Dave,

Not entirely clear what you need but for multiple criteria SUMPRODUCT is often the answer.

If you are not familiar try

http://xldynamic.com/source/xld.SUMPRODUCT.html

tells you all you need to know

otherwise post an example as a zipped file

Ed