How to total Items in a List "not that easy"

[TK]

Hi:

At first glance this question seems below this group's level , but it's
really not that simple, the list can go on and on and the items are numerous.


What would be the better way to sum the amounts Needed "Column E"
for the individual items in column A!.

A B C D E
1 Material Length Height SqFT Needed
2 Plywood 8 8 0.03 2
3 Block 4 2 1.13 9
4 Brick 10 10 6.75 675
5 Plywood 4 8 0.03 1
6 Block 4 12 1.13 54
7 Brick 10 12 6.75 810

Results Being:

Plywood 3
Block 63
Brick 1485


The result could be written to an obscure part of the worksheet
for the tally and reference if need be.

I tried to use some routines with dsum and subtotal but my solutions seemed
pretty clumsy.

Thanks

 

[sebastienm]

Hi,
Several methods:
1. Single Criterium
As in your example, you have only 1 criterium eg: count when a1:a7="Plywood"
=COUNTIF(a1:a7,"Plywood")
If you want to sum the total lenght for plywood:
=SUMIF(a1:a7,"Plywood",b1:b7) (will sum b1:b7, which may be
meaningless but just for example)
Instead of exact match, you can also check for >=, >, <, <=
eg: =SUMIF(a1:a7,">=Plywood",b1:b7) in case you eg Plywood1 and Plywood2
And finally in your example, assuming "Plywood" is entered in A10
=COUNTIF(a1:a7,A10)

2. Multiple Criteria
Use SUMPRODUCT function (see online help)

3. Pivot Table
-Select your data and header
-menu Data > Pivot Table
- Step 1of 3 of the Pivot dialog: choose MS Excel list or database. Click Next
- Step 2 of 3: that's the data source which is by default the selected
range. Good. click next.
- Step 3 of 3: in 'Layout' , put 'Material' in Row Data, and put 'Material'
again in Data area. Click finish.
(in this last step, adding a non-numeric field to the Data area
automatically count it, adding a numeric field would sum it by default)

Regards,
Sebastien

 

[Tom Ogilvy]

You can create a list of unique entries from column A by selecting column A
and doing

Data=>Filter=>Advanced Filter

select copy to another location - click in the textbox and designate a
location
leave the criteria blank
check the Uniques checkbox on the lower left

Click OK

Assume the first material is in M2
in N2 put in the formula

=Sumif(A:A,M2,E:E)

then drag fill this down

 

[TK]

Perfect: Tom

Thank you!

You can create a list of unique entries from column A by selecting column A
and doing

Data=>Filter=>Advanced Filter

select copy to another location - click in the textbox and designate a
location
leave the criteria blank
check the Uniques checkbox on the lower left

Click OK

Assume the first material is in M2
in N2 put in the formula

=Sumif(A:A,M2,E:E)

then drag fill this down