Conditional Summing reminder/refresher (please!)

[LeeHarris]

I've got about 500 rows of data with about a dozen columns. There are
about 100 "items" in the column "A", repeated several times each (hence
the 500 rows), with different rows of data each time

I want to be able to sum up each column for each entry in A

I've already used advanced filter -> copy list -> unique records only,
so I've set up a unique list of "items", I just want to now be able to
say "look through the 500 rows, every time you find THIS item name, sum
THIS column)

So I will end up with a unique list of items, and a sum total of the
values in each column for that item only

I couldn't remember how to use SUMIF properly, or maybe it needs an
array formula or that clever one SUMPRODUCT?

 

[Mikeopolo]

Whilst I do use sumproduct at times, I feel that a pivot table with Col
A as the rows, and each of the other columns as the data field (sumof)
would be a better solution.

Regards
Mike

 

[Roger Govier]

Hi Lee

Sounds an ideal problem for using a Pivot Table.
For help on setting up Pivot Tables take a look at Debra Dalgleish's
site
http://www.contextures.com/tiptech.html
and scroll to section on Pivot Tables
http://peltiertech.com/Excel/Pivots/pivotstart.htm

There is also an excellent video tutorial at Mike Alexander's site
http://www.datapigtechnologies.com/ExcelMain.htm
http://www.datapigtechnologies.com/flashfiles/pivot1.html

 

[Bob Phillips]

Sounds like SUMIF to me

=SUMIF($A:$A,$M1,B:B)

just copy down and across


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)