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