First you need to remove the w, the h, and the qty from the numbers in the
table, Excel wants to treat those as text instead of numbers.
A simple macro can do the job; the labels should be at the top of the
columns, not inside the cells.
Let us know if you need help with the conversion of your table to real
numbers.
This:
In a table with dimensions and quantities such as:
45w 30h 10qty
30w 45h 10qty
45w 30h 2qty
45w 30h 8qty
Should become something like this:
C D E
5 w h qty
6 45 30 10
7 30 45 10
8 45 30 2
9 45 30 8
Assuming your table looks like this, then you would use an array formula
version of the SUM formula and the IF formula. The easiest way to do this is
by using the Conditional Sum wizard.
In Excel 2003, the Conditional Sum Wizard is found in Tools|Conditional Sum.
If you do not find it there, go to Tools|Add-Ins and click Conditional Sum
Wizard to make it available.
Before using the wizard, prepare a place in your worksheet for the answer.
As a personal preference, I find it handy to leave space above and to the
left of major spreadsheet elements such as tables. It makes it much easier to
add things around the edges of the table later, instead of trying to move the
whole mess around. In this example, I went to the space that I left above my
table and prepared C2 through D4 like this, with simple labels and empty
spaces to the right of them for entries and answers. The wizard will fill
these in for you in a minute.
C D
2 Width
3 Height
4 Quantity
After you call the wizard using Tools|Conditional Sum:
The wizard is pretty easy to follow; it will ask you to select the whole
table containing both the conditional and summing data, including the
headers. In this example, using the w-h-qty data table, you would select
Cells C5 through E9. .
The wizard will give you a drop-down selector to pick the column you want to
sum; in this case it is qty.
The wizard will give you a way to add conditions. The selectors will be
Column; Is; and This Value. The column selector will have a dropdown list of
the columns in your table. In the example above, all three columns, h, w. and
qty are available for conditional evaluation. Select your column w.
The Is selector will give you a choice, using symbols, of ‘equal to’,
‘greater than’, ‘less than’, ‘greater than or equal to’, ‘less than or equal
to’, and ‘not equal to’. The default is ‘equal to’ (=) and you can leave it
alone.
The This Value selector will have a list of all of the values in the table,
You just select the one that you want. In this case, it is 45.
When you are finished setting up the first condition, click the Add
Condition button, the new condition will appear in the condition list window.
When w the condition is showing in the window, set up the h condition the
same way. You should end up with two conditions showing in the window, h=30,
and w=45. They will have re-arranged themselves in alphabetical order, not in
as-entered order.
When you have all of the conditions entered into the wizard, click next. You
will now have two choices. The first one just enters the formula into a cell
of your choice. The second does something really special. The wizard will
adjust the formula to accommodate the use of cell references instead of hard
numbers in the conditions. This means that you can change the lookup without
going into the formula and finding the conditions and changing them there.
This is why I always select the second option: “Copy the formula and
conditional valuesâ€. After you select the formula and conditional values
option, click Next.
The next wizard page asks you for a location to copy the ‘w’ condition to,
remember that in this example I left an empty cell in D2 with a label “Widthâ€
right next to it. So I clicked on Cell D2 and clicked Next.
The next wizard page asks for a destination for the ‘h’ condition, I select
D3 and click Next. As I do this, the wizard is entering 45 and 30 in the
appropriate cells.
The next wizard page asks for a destination for the conditional sum formula.
I left D4 empty for that purpose, with the Quantity label next to it. Click
D4 and click Finish.
It should look like this:
C D E
2 Width 45
3 Height 30
4 Quantity 20
5 w h qty
6 45 30 10
7 30 45 10
8 45 30 2
9 45 30 8
In my example, I got this formula in Cell D4:
{=SUM(IF($C$6:$C$9=D2,IF($D$6:$D$9=D3,$E$6:$E$9,0),0))}
Note that the formula has cell references instead of numbers for conditions.
The sum is 20 (10+2+8). At this point, you can change the Width and Height
conditions to sum the Quantity for a different size. (In this example,
changing the numbers from Width 45 and Height 30 to Width 30 and Height 45
gives you a 10 in the Quantity box.)
Change the numbers in Cells D2 and D3 to look up different sizes:
C D E
2 Width 30
3 Height 45
4 Quantity 10
5 w h qty
6 45 30 10
7 30 45 10
8 45 30 2
9 45 30 8
Back to the formula:
Note the curly brackets surrounding the formula; you cannot enter these
curly brackets yourself. Well, you can but the formula won’t work. If you
have to edit the formula, or create your own array formula, you will need to
click Shift-Control-Enter to finish the formula, excel adds the curly
brackets to show that it has converted it to an array formula based on your
shift-control-enter signal.
For further reading, search Excel Help for ‘Array Formula’ and for
‘Conditional Sum’. In the wizard itself, help is available using the question
mark button.
Hope this helps. Let us know if you need clarification or more help.
SongBear
