Re: Counting

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Debra Dalgleish (dsd_at_contexturesXSPAM.com)
Date: 10/02/04 

Date: Sat, 02 Oct 2004 17:19:17 -0400

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

James wrote:
> I have an excel spread*** 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 spread*** 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
>
> 

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
Quantcast