Counting

[James]

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

 

[Debra Dalgleish]

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]

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

 

[Dave Peterson]

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.

 

[Debra Dalgleish]

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.

 

[James]

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 ).

Regards

James

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.