Summary of List - How To?

[fkrueger]

I have a list I am trying to summarize. It lists customer numbers i
col A, and item numbers in col B. The list may have many entries for
specific customer and each customer may have any number of items. Eac
item is in a category I am adding in col C, and the item price is th
list, in col D.

I am trying to summarize the data by listing the customers in, say, co
R. Col S should indicate how much this customer spent on items i
category one, Col T should indicate how much they spent in categor
two, etc. Obviously the categories are limited, currently there ar
only five.

I have been unable to use =DSUM without continually replicating th
criteria field. Neither can I make any sense out of a pivot table t
give proper structure to the output. If any of you wizards have an
ideas I would certainly appreciate a hint or two! Thanks in advance.
Frustrated Fred

 

[Frank Kabel]

Hi
this sound like pivot tables are what you're looking for. See the
following for more details:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

 

[Jim Cone]

Fred,

Have you tried sub-totaling the list?
Go to Data | Subtotals

Regards,
Jim Cone
San Francisco, CA

----- Original Message -----
From: "fkrueger >" <<[email protected]>
Newsgroups: microsoft.public.excel.misc
Sent: Wednesday, March 17, 2004 1:29 PM
Subject: Summary of List - How To?

 

[Debra Dalgleish]

Instead of using DSUM, you could use the SUMPRODUCT function in your
summary table. For example, in cell S2, enter the following formula,
where the data is in cells A225, customer numbers are in column R, and
the categories are in row 1:

=SUMPRODUCT(($A$2:$A$25=$R2)*($C$2:$C$25=S$1)*($D$2:$D$25))

Copy the formula across to the last category, and down to the last customer.

 

[Aladin Akyurek]

I have been unable to use =DSUM without continually replicating the

criteria field.

See my contrib in

http://www.mrexcel.com/board2/viewtopic.php?t=60895

for various approaches that enables you tu use DSUM "without continually
replicating the criteria field."