 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2004Counting
I have an excel spreadsheet which I am developing as a record of hills I have climbed. The hills are broken down into geographic areas and the list can be sorted by a variety of criteria (height, area, distance from a given point, etc). Each hill has a unique reference and the first 4 characters identify the area. Each time a hill is climbed, a date is entered in a column and the count function is used in a summary area to show how many hills have been climbed in a given area. I have given this to some others in my club and they have pointed out that if the spreadsheet is sorted by any criteria other than area then the summary gives the wrong answer. How can I use formulae to produce the summary at the bottom from the table below, in such a fashion that the result will be correct no matter how the table is sorted? I am not averse to using intermediate columns but do not want to use a macro. I have already created an extra column (N) showing just the first four characters of the reference so that I could use =COUNTIF($N$21:$N$198,"G/CE") to get the total number of hills, but I cannot figure how to do something similar to count those climbed. Thanks in Advance James Reference Name Points WAB Maidenhead Date G/CE-001 Cleeve Hill 1 SO92 IO81XW 01/06/04 G/CE-002 Walton Hill 1 SO97 IO82WJ G/CE-003 Bredon Hill 1 SO94 IO82XB G/CE-004 Bardon Hill 1 SK41 IO92IR G/CE-005 Haddington Hill 1 SP80 IO91PS 08/06/04 G/DC-001 High Willhays 4 SX58 IO70XQ G/DC-002 Brown Willy 1 SX18 IO70QO G/DC-003 Kit Hill 1 SX37 IO70UM 09/06/04 G/DC-004 Hensbarrow Beacon 1 SW95 IO70OJ G/DC-005 Christ Cross 1 SS90 IO80GU 12/06/04 G/DC-006 Carnmenellis 1 SW63 IO70JE G/DC-007 Watch Croft 1 SW43 IO70ED G/LD-001 Scafell Pike 10 NY20 IO84JK G/LD-003 Helvellyn 10 NY31 IO84LM 23/06/04 G/LD-004 Skiddaw 10 NY22 IO84KP G/LD-005 Great Gable 8 NY21 IO84JL G/LD-006 Pillar 8 NY11 IO84IL 14/09/04 G/LD-007 Fairfield 8 NY31 IO84ML G/LD-008 Blencathra 8 NY32 IO84LP G/LD-009 Grasmoor 8 NY12 IO84IN Area Hills Climbed Remaining Central England (G/CE) 5 2 3 Devon and Cornwall (G/DC) 7 2 5 Lake District (G/LD) 8 2 6 You could create a pivot table from the data, with Area in the row area, and Count of Date climbed in the data area. There are instructions and links on Jon Peltier site: http://www.peltiertech.com/Excel/Pivots/pivotstart.htm > I have an excel spreadsheet which I am developing as a record of hills I > have climbed. The hills are broken down into geographic areas and the list [quoted text clipped - 93 lines] > > Lake District (G/LD) 8 2 6  SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks Debra I did think of a pivot table but it doesn't really solve my issue as it needs to be updated each time the data changes. What I am trying to achieve is something that automatically updates the summary when the data changes. The idea is to minimise user interaction so that all they have to do is either enter date climbed, click a button to sort data by 2 or 3 predetermined criteria or click a link that takes them to a map of the hills they want to climb. cheers James > You could create a pivot table from the data, with Area in the row area, > and Count of Date climbed in the data area. [quoted text clipped - 100 lines] > > > > Lake District (G/LD) 8 2 6 Debra has some instructions on how to use dynamic ranges that will adjust size when you add/delete rows at: http://www.contextures.com/xlNames01.html#Dynamic And you could use an worksheet_activate event to update the pivottable when you activate the sheet that holds the pivottable. (I'm guessing that the data is on a separate worksheet.) Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT End Sub Just rightclick on the worksheet tab that holds the pivottable. Select view code and paste this into the code window. Then select your worksheet with your data, make a change (remember what it is so you can change it back), then select your pivottable worksheet. > Thanks Debra > [quoted text clipped - 131 lines] > > Excel FAQ, Tips & Book List > > http://www.contextures.com/tiptech.html SignatureDave Peterson ec35720@msn.com You could use the Sumproduct function to get the total-- In your summary table include a separate column with area codes. This example is in cells A2:E5 Area Code Hills Climbed Remaining Central England G/CE Devon and Cornwall G/DC Lake District G/LD In the hills column, you can modify your existing Countif formula to refer to the area code column, e.g.: =COUNTIF(N21:N198,B2) In the climbed column, use a sumproduct formula: =SUMPRODUCT(($N$21:$N$198=B2)*($O$21:$O$198<>"")) where date climbed is in column O. > Thanks Debra > [quoted text clipped - 146 lines] >>> >>>Lake District (G/LD) 8 2 6 SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks Debra That works exactly the way I want. I just love Excel. Every time I write a new spreadsheet I get to learn a new function :o). Regards James > You could use the Sumproduct function to get the total-- > [quoted text clipped - 167 lines] > >>> > >>>Lake District (G/LD) 8 2 6 You're welcome! Thanks for letting me know that it worked. > Thanks Debra > [quoted text clipped - 192 lines] >>>>> >>>>>Lake District (G/LD) 8 2 6 SignatureDebra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.