In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

if you have as a setup then =sumproduct((a2:a22=45)*(b2:b22=30)*c2:c22)
a b c
45 30 10
if you have the text as shown then it will be a bit more difficult
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007.

"Don Guillett" wrote:

if you have as a setup then =sumproduct((a2:a22=45)*(b2:b22=30)*c2:c22)
a b c
45 30 10
if you have the text as shown then it will be a bit more difficult
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

Try mine
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007.

"Don Guillett" wrote:

if you have as a setup then =sumproduct((a2:a22=45)*(b2:b22=30)*c2:c22)
a b c
45 30 10
if you have the text as shown then it will be a bit more difficult
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

Assuming that in your sample the "-" represents a cell delimiter.

=SUMPRODUCT(--(A1:A4&B1:B4="45w30h"),--SUBSTITUTE(UPPER(C1:C4),"QTY",""))

Returns 20

--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

What is the format of your data? Is it really a table, or do you have all
the data for each entry in a single cell, separated by spaces and hyphens as
you've shown? Do the entries have the w, h, and qty text? If so, it can
still be done, but I hope you realise that it would be easier if you merely
had the numbers in appropriate columns. [And if the data did include those
text strings I would feel inclined to use Data/ Text to Columns to split it
up more sensibly.]
--
David Biddulph

"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007.

"David Biddulph" wrote:

What is the format of your data? Is it really a table, or do you have all
the data for each entry in a single cell, separated by spaces and hyphens as
you've shown? Do the entries have the w, h, and qty text? If so, it can
still be done, but I hope you realise that it would be easier if you merely
had the numbers in appropriate columns. [And if the data did include those
text strings I would feel inclined to use Data/ Text to Columns to split it
up more sensibly.]
--
David Biddulph

"Paul" wrote in message
...
In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

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 wont 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


"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

Thanks for the info. I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007 and I don't think the
Conditional Sum Wizard is available; at least I couldn't find it.


"SongBear" wrote:

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 wont 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


"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

Paul, if you are still there, I think we are zeroing in on something:

The formula given to you by Biff/T. Valko may work in Excel2007, but not in
2003, so I cannot test that against a unique list. I did find a way to create
a unique list from the original list of 500 plus cells. To test it and help
generate a step-text, I generated (typed random numbers then copy-pasted) an
artificial 120 or so line list as such:

w h qty
45 30 10
30 45 10
45 30 2
45 30 8
54 21 5
45 30 4
30 45 6
45 30 2
45 30 8
54 21 1
45 30 5
30 45 7
45 30 10
45 30 10
54 21 2
45 68 8
23 94 5
28 70 4
45 21 6
30 33 2
45 68 8
45 94 1
Copy/pasted down to about 120 items long€¦

I then used the Advanced Filter tool, hopefully this is still available in
Excel2007.

First, highlight the first two columns of your data (ONLY highlight columns
€˜w and €˜h, all the way down using control/shift/downarrow).
With only w and h selected, click Data|Filter|Advanced Filter. When the
Advanced Filter dialog box comes up, notice that the List range is already
filled in with your two data columns.
The Advanced Filter tool will allow you to place the results in a different
location. I went to the side in empty neighboring cells. First I checked
€˜Copy to another location, then I selected an empty cell to the right of my
original table. Important to remember this as this tool will copy its
results over the original table if you forget to tell it not to.

In the Advanced Filter dialog, check the €˜Unique records only box. Only do
this after you have given it a copy to location other than the original table.

Ignore criteria range.

Click OK. I generated a list of the unique sizes in my made up table. This
is 11 unique sizes filtered out of about 120 mixed lines.

w h
45 30
30 45
54 21
45 68
23 94
28 70
45 21
30 33
45 94
54 70
22 33

Next you want to count the total quantity of each size. Since my copy of
Excel does not have the SUMIFS function, I had to go back to the
sum(if(€¦formula.

First I sorted the new unique values table so the sizes would be in order.
(Click in the new table; selecting the €˜w cell is enough. Then click
Data|Sort, the sorting tool will automatically come up with the w as the
first sort option for the list. Select €˜h in the €œThen by€� box. Then OK to
sort the list by sizes.

w h
22 33
23 94
28 70
30 33
30 45
45 21
45 30
45 68
45 94
54 21
54 70

I then placed my original wizard generated formula in the first cell to the
right of the new table of unique sizes.

w h Total Qty
22 33 SUM(IF($C$6:$C$9=D2,IF($D$6:$D$9=D3,$E$6:$E$9,0),0 ))
23 94
28 70
30 33
30 45
45 21
45 30
45 68
45 94
54 21
54 70

I modified the formula to point to the new longer list and to the criteria
in the cells to the left of the formula.

w h Total Qty
22 33 SUM(IF($C$6:$C$126=H7,IF($D$6:$D$126=I7,$E$6:$E$12 6,0),0))
23 94
28 70


I then placed an equal sign in front of the word SUM and hit
shift-control-enter to create the array formula.

w h Total Qty
22 33 41
23 94
28 70

I then double clicked on the bottom right corner of the cell with the new
formula. This copied the new array formula down to the rest of the cells next
to the new table.

w h Total Qty
22 33 41
23 94 47
28 70 38
30 33 35
30 45 69
45 21 50
45 30 178
45 68 93
45 94 40
54 21 48
54 70 51

Next I created a new array formula to count the number of occurrences of
each size and put it on the left of the top cell of the unique sizes table.

COUNT(IF(($C$6:$C$126=H7)*($D$6:$D$126=I7),$D$6:$D $126))

Note that the cell references depend on the location of your original data
table and of the new unique sizes table. Note that the data table cell
references are absolute (with dollar signs) and the references to the
criteria in the unique table are relative references.

After the equal sign was added in front of the word COUNT, this formula also
had to be shift-control-entered to make it an array.

Count w h Total Qty
7 22 33 41
23 94 47
28 70 38

After the array was created for the first size row, I double clicked the
bottom right corner of that cell and Excel copied the array formula down.

Count w h Total Qty
7 22 33 41
8 23 94 47
8 28 70 38
8 30 33 35
10 30 45 69
8 45 21 50
30 45 30 178
16 45 68 93
8 45 94 40
10 54 21 48
8 54 70 51

This should give you a Unique Sizes Table with a count of occurrences and a
total inventory sum.

This Advanced Filter is not €œlive€� like a formula, so you will have to
re-run the filter tool occasionally, as appropriate, if the list changes.
This could be automated with a macro.

Hope this helps, let us know if you need clarification or further help.

SongBear
*****************************************


"Paul" wrote:

Thanks for the info. I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007 and I don't think the
Conditional Sum Wizard is available; at least I couldn't find it.


"SongBear" wrote:

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 wont 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


"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

SongBear,
I just read your response after posting a new question that describes my
problem a bit more clearly. I'll study your most recent post and try to
figure it out. If you have a chance, take a look at my recent post; perhaps
it may clarify my dilemma. Paul

"SongBear" wrote:

Paul, if you are still there, I think we are zeroing in on something:

The formula given to you by Biff/T. Valko may work in Excel2007, but not in
2003, so I cannot test that against a unique list. I did find a way to create
a unique list from the original list of 500 plus cells. To test it and help
generate a step-text, I generated (typed random numbers then copy-pasted) an
artificial 120 or so line list as such:

w h qty
45 30 10
30 45 10
45 30 2
45 30 8
54 21 5
45 30 4
30 45 6
45 30 2
45 30 8
54 21 1
45 30 5
30 45 7
45 30 10
45 30 10
54 21 2
45 68 8
23 94 5
28 70 4
45 21 6
30 33 2
45 68 8
45 94 1
Copy/pasted down to about 120 items long€¦

I then used the Advanced Filter tool, hopefully this is still available in
Excel2007.

First, highlight the first two columns of your data (ONLY highlight columns
€˜w and €˜h, all the way down using control/shift/downarrow).
With only w and h selected, click Data|Filter|Advanced Filter. When the
Advanced Filter dialog box comes up, notice that the List range is already
filled in with your two data columns.
The Advanced Filter tool will allow you to place the results in a different
location. I went to the side in empty neighboring cells. First I checked
€˜Copy to another location, then I selected an empty cell to the right of my
original table. Important to remember this as this tool will copy its
results over the original table if you forget to tell it not to.

In the Advanced Filter dialog, check the €˜Unique records only box. Only do
this after you have given it a copy to location other than the original table.

Ignore criteria range.

Click OK. I generated a list of the unique sizes in my made up table. This
is 11 unique sizes filtered out of about 120 mixed lines.

w h
45 30
30 45
54 21
45 68
23 94
28 70
45 21
30 33
45 94
54 70
22 33

Next you want to count the total quantity of each size. Since my copy of
Excel does not have the SUMIFS function, I had to go back to the
sum(if(€¦formula.

First I sorted the new unique values table so the sizes would be in order.
(Click in the new table; selecting the €˜w cell is enough. Then click
Data|Sort, the sorting tool will automatically come up with the w as the
first sort option for the list. Select €˜h in the €œThen by€� box. Then OK to
sort the list by sizes.

w h
22 33
23 94
28 70
30 33
30 45
45 21
45 30
45 68
45 94
54 21
54 70

I then placed my original wizard generated formula in the first cell to the
right of the new table of unique sizes.

w h Total Qty
22 33 SUM(IF($C$6:$C$9=D2,IF($D$6:$D$9=D3,$E$6:$E$9,0),0 ))
23 94
28 70
30 33
30 45
45 21
45 30
45 68
45 94
54 21
54 70

I modified the formula to point to the new longer list and to the criteria
in the cells to the left of the formula.

w h Total Qty
22 33 SUM(IF($C$6:$C$126=H7,IF($D$6:$D$126=I7,$E$6:$E$12 6,0),0))
23 94
28 70


I then placed an equal sign in front of the word SUM and hit
shift-control-enter to create the array formula.

w h Total Qty
22 33 41
23 94
28 70

I then double clicked on the bottom right corner of the cell with the new
formula. This copied the new array formula down to the rest of the cells next
to the new table.

w h Total Qty
22 33 41
23 94 47
28 70 38
30 33 35
30 45 69
45 21 50
45 30 178
45 68 93
45 94 40
54 21 48
54 70 51

Next I created a new array formula to count the number of occurrences of
each size and put it on the left of the top cell of the unique sizes table.

COUNT(IF(($C$6:$C$126=H7)*($D$6:$D$126=I7),$D$6:$D $126))

Note that the cell references depend on the location of your original data
table and of the new unique sizes table. Note that the data table cell
references are absolute (with dollar signs) and the references to the
criteria in the unique table are relative references.

After the equal sign was added in front of the word COUNT, this formula also
had to be shift-control-entered to make it an array.

Count w h Total Qty
7 22 33 41
23 94 47
28 70 38

After the array was created for the first size row, I double clicked the
bottom right corner of that cell and Excel copied the array formula down.

Count w h Total Qty
7 22 33 41
8 23 94 47
8 28 70 38
8 30 33 35
10 30 45 69
8 45 21 50
30 45 30 178
16 45 68 93
8 45 94 40
10 54 21 48
8 54 70 51

This should give you a Unique Sizes Table with a count of occurrences and a
total inventory sum.

This Advanced Filter is not €œlive€� like a formula, so you will have to
re-run the filter tool occasionally, as appropriate, if the list changes.
This could be automated with a macro.

Hope this helps, let us know if you need clarification or further help.

SongBear
*****************************************


"Paul" wrote:

Thanks for the info. I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007 and I don't think the
Conditional Sum Wizard is available; at least I couldn't find it.


"SongBear" wrote:

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

Thanks for your info. I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007 and I don't believe the
Conditional Sum Wizard is available; at least I can't find it.

"SongBear" wrote:

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 wont 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


"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

I'm also using Excel 2007

No need for conditional sum wizards.

A2:A5 = "w" values
B2:B5 = "h" values
C2:C5 = qty

List the unique combinations in say, E2:Fn like this:

......E.....F
2..45...30
3..30...45

Enter this formula in G2 and copy down as needed:

=SUMIFS(C$2:C$5,A$2:A$5,E2,B$2:B$5,F2)


--
Biff
Microsoft Excel MVP


"Paul" wrote in message
...
Thanks for your info. I think I probabaly needed to clarify a few things.
My entry is thus: 45 30 10 in separate cells.
The main problem is that I have about 500 lines in this sheet and I'd like
the program to search for all the different dimensions and give me a total
for each unique dimension. I'm also using Excel 2007 and I don't believe
the
Conditional Sum Wizard is available; at least I can't find it.

"SongBear" wrote:

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


"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

Paul, if I understand your problem correctly, I'd probably add a sorter
column concatenating columns A and B and sort on that column. Then you could
subtotal quantity at each change in the sorter column.

"Paul" wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

On Tue, 12 Feb 2008 09:53:02 -0800, Paul
wrote:

In a table with dimensions and quantities such as:
45w - 30h - 10qty
30w - 45h - 10qty
45w - 30h - 2qty
45w - 30h - 8qty
How can I get a total quantity for the 45 x 30 size?

I see that you are storing just numbers in separate cells

One possible solution would be to use a pivot table.

Drag the heights to the Rows area; width's to the Columns area; and qty to the
Data area.

At any intersection you will see the Sum of qty for that combination.
--ron