formula help

[Accesscrzy]

This is a condensed version of an inventory sheet. It is an inventory of 5
different machines. The sheet is one continuous sheet. Management just
wants the total count of each candy charted out by date, by candy.
So there will be a line for Snickers plotting the count for 10/2, 10/9,
10/16, 10/23.
Then another line for Baby Ruth plotting the count for 10/2, 10/9, 10/16,
10/23.
Ect…
I am assuming I will have to insert a Bin worksheet to capture the data and
sort it. I am lost as to where to start.
Any help would be GREATLY appreciated!

(The chart is not really the issue. I think I can figure that out by using
series???)
Candy Count Date
Snickers 40 10/2/2008
Baby Ruth 33 10/2/2008
M&Ms 33 10/2/2008
Butterfig 2 10/2/2008
Snickers 59 10/9/2008
Baby Ruth 48 10/9/2008
M&Ms 58 10/9/2008
Butterfig 43 10/9/2008
Snickers 12 10/16/2008
Baby Ruth 30 10/16/2008
M&Ms 10 10/16/2008
Butterfig 6 10/16/2008
Snickers 8 10/23/2008
Baby Ruth 12 10/23/2008
M&Ms 20 10/23/2008
Butterfig 3 10/23/2008

 

[Bernard Liengme]

Let's say your data is in A2:C500 (A = candy, B =count C = date) with
headers in tow 1


In enter the list of dates to plot in row 1 starting in F1
In E2 and down enter the unique candy names
In F2 use =SUMPRODUCT(--($A2:$A100=$E2),--($C$2:$C$100=F$1), $B$2:$B$100)
Copy down and across
For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html


or make a pivot table

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[ShaneDevenshire]

Hi,

A pivot table is an elegent solution

Suppose your data is in A1:C17, highlight it and choose
Data, Pivot Table and Pivot Chart Report, click Next, click Next
Click the Layout button and drag the Candy field button to the Row area, the
Date field button to the Column area, and the Count button to the Data area,
Click OK, choose Existing worksheet and pick a cell where you want this and
press Finish.

If this helps, please click the Yes button.

 

[Accesscrzy]

I could not get the SUMPRODUCT to work, so I tried the pivot table. It works
great. Such a simple answer. I had to create another sheet to link to the
pivot table. The pivot table has a mind of its own when it comes to charts!
I made everything work together and even found the box to check to
automatically refresh the pivot table. The only problem is that it doesn't
always work. Sometimes I have to manually go in and do a refresh. So this
is just a temporary fix until I can figure out the SUMPRODUCT route. I know
its the solution... just need to keep working it. I want the chart to
automatically update (realtime).