Counting #4

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


0
James8691 (3)
10/2/2004 9:03:40 PM
excel 39879 articles. 2 followers. Follow

6 Replies
415 Views

Similar Articles

[PageSpeed] 3

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 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/2/2004 9:19:17 PM
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


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:415F1B55.606@contexturesXSPAM.com...
> 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 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
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
James8691 (3)
10/3/2004 9:17:03 AM
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.




James wrote:
> 
> 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
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:415F1B55.606@contexturesXSPAM.com...
> > 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 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
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/3/2004 11:33:48 AM
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 wrote:
> 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
> 
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:415F1B55.606@contexturesXSPAM.com...
> 
>>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 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
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


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

0
dsd1 (5911)
10/3/2004 11:55:57 AM
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

"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:415FE8CD.4000806@contexturesXSPAM.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.
>
> James wrote:
> > 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
> >
> >
> > "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> > news:415F1B55.606@contexturesXSPAM.com...
> >
> >>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 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
> >>Excel FAQ, Tips & Book List
> >>http://www.contextures.com/tiptech.html
> >>
> >
> >
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
James8691 (3)
10/4/2004 10:14:51 PM
You're welcome! Thanks for letting me know that it worked.

James wrote:
> 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
> 
> "Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
> news:415FE8CD.4000806@contexturesXSPAM.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.
>>
>>James wrote:
>>
>>>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
>>>
>>>
>>>"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
>>>news:415F1B55.606@contexturesXSPAM.com...
>>>
>>>
>>>>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 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
>>>>Excel FAQ, Tips & Book List
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>
>>>
>>>
>>
>>-- 
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
>>
> 
> 
> 


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

0
dsd1 (5911)
10/4/2004 10:45:57 PM
Reply:

Similar Artilces:

how do i count numbers that contain 02 (i.e.7029955)?
how do i count numbers that contain 02 (i.e.7029955)? I tried =COUNTIF(C143:C154,"?02????") but it did not work. The system seems not to recognize the ? wildcard. Try: =SUMPRODUCT(--ISNUMBER(SEARCH("02",C143:C154))) -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "caliche" <caliche@discussions.microsoft.com> wrote in message news:C7563017-D686-4B64-9DBB-197F84DD3127@microsoft.com... > how do i count numbers that contain 02 (i.e.7029955)? I tried > =COUNTIF(C143:C154,"?02????") but...

Counting with multiple criteria
Would be grateful if someone can help me with this A B C D 8-9AM 9-10AM 1 CA03 ON CA03 OF 2 CA03 OF CA03 OF 3 CA03 ON CA03 TR 4 CA03 TR CA03 TR 5 CA05 OF CA04 OF 6 CA05 TR CA05 ON 7 CA04 OF CA05 TR Note: Column A-B has the same header row, and so does C-D I want to count in the range (A1:D7) - How many CA03 and TR: [the result should be 3] - How many CA03 and ON and OF: [the result should be 5] Thanks in advance for your help. Best regards, Thuy Assuming your table is in A1:D8, data in rows 2 to 8 (in A2:D8) > ...

Read Receipts #4
How do I turn off read receipts? I would like to insure the sender does not know if I read the email sent to me. Thx in advance -d You could either swich it off globally in your exchange settings or use tools like aloaha to remove the delivery/read receitpt requests from incoming mails. FH "rgm" <rgmcleod@austin.rr.com> schrieb im Newsbeitrag news:eXvM$OvfEHA.2352@TK2MSFTNGP09.phx.gbl... > How do I turn off read receipts? I would like to insure the sender does not > know if I read the email sent to me. > Thx in advance > > -d > > How do you switch i...

Report available for items IN a stock count?
Is there any report already built in to Dynamics that will show us a particular vendor's items that are currently in a stock cycle count? Or show us if it's ever BEEN in a stock count (other than annual inventory) Is there something in SmartList? We are using v9.0 I could create one via MS Access if someone could tell me which tables would be the ones to use? Pam, From http://victoriayudin.com/gp-reports/inventory-tables/: IV00103 - Item Vendor Master IV10300 - Unposted Stock Count (header) IV10301 - Unposted Stock Count (line detail) IV30700 - Stock Count History (header) IV...

OST file #4
I deleted my Exchange mail account because the corporate exchange server harddrive crashed and I now need access to my outlook.ost file. I have tried Norton and "recover my files" with no success. Does anyone have any suggestions on how to recover the .ost which is 400 MB? Thank you for your time. - Mark Adkins Fixing this problem is critical to my employment. Can anyone direct me to someone who may be able to help? >-----Original Message----- >I deleted my Exchange mail account because the corporate >exchange server harddrive crashed and I now need access >to m...

How to move our data from Online CRM 4.0 to On Premise 4.0
Greetings everyone, We have been evaluating the on line version of Microsoft Dynamics CRM 4.0 for almost a month. The 12 employees who were selected for the test period spent many hours on uploading their contacts and calendar to the online website. Now we decided to use the on premise version and need to export all the data out to the online account and import to on premise. I was wondering if anyone can direct me to a document or any kind of resources that explains the process. Thank you, E. I was wondering about this too a few months ago. There are tools available for moving fro...

page numbering #4
As usual, I am having a difficult time doing a simple task in MS Office. In Publisher 2002, how do you start page numbering on a page other than page 1? This is easy to do in Word (start page numbering on page __) and troublesome in Publisher 2000 (tools>options menu), but in Publisher 2002 I have not been able to find any help on how to do this. How do I start numbering with "1" on say, page 6? I am replying to my own question, in case any one else needs the answer! Go to "insert>section" and figure it our from there (like I did). Oh and probably look forw...

VBA Question #4
Hi: I have BASIC programming skills but have never had the opportunity to use VBA inside Excel. Using a UDF, I need to know how to take the contents of a cell and pass it to a variable inside my user defined function. I am trying to take the results in one cell and if it matches, then take the information from two other cells and multiply them together. I've poured over the VBA documentation and see how to push a value into a cell, but can't find any examples of retrieving a cell's content. Thanks in advance! -- Steve Spence Independent AMSOIL Dealer AMSOIL - The "...

Help with count formula please,
OFFSET(C6,COUNT(C7:C33),0) I am using this formula as part of a spreadsheet I am using for a social golf club, it has 20 sheets, and this formula keeps track of how many games an individual plays. It works well except for the fact if a player misses a game the result (in C35 shows 0, as per the formula) eg: c7 1 c8 2 c9 3 c10 4 and the result in c35 is 4 ( as I want) but if c7 1 c8 2 c9 (no input,blank) c10 3 the result in c35 is 0 not 3 So what I need is to change the ,0) bit but I am not sure what with or how to replace it so that it ignores a blank cell ......... Thanks for any help Ke...

Counting spefic cell (not a range) with a value greater than 1
I am looking for a formula to count specific cells and a range (A5, A10, A15, A20 and so on) but only count when the value is greater than $1.00. So if the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value of cell A15 was $0.50 and the value of cell A20 was $2.00 the total count would be 3. I'm stumped! =SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUNT(A5,A10,A15,A20,A25))))>1)) however if you want to sum every 5th cell greater than 1 you can use =SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A50>1)) -- Regards, Peo Sjoblo...

Does CRM 4.0 work well with Office 2003?
Hi all, We're looking at upgrading from CRM 3 to 4.0. Most of our users are at MS Office 2003 and we'd like to keep that way for now. I understand 4.0 is supposed to be "compatible" with MS Office 2003 but I've been researching the issues. Are there any known compatibility issues between CRM 4.0 and MS Office 2003? Will using 2003 instead of 2007 cause any loss of functionality in CRM? (Such as disabling the tracking token.) Thank you, Mohamed Both Outlook 2003 and 2007 works fine with Microsoft CRM 4.0. We have a mix of Outlook versions being used everyday for the l...

Count consecutive cells
I have a table which tracks the attendance at an event over the years. Each time someone attends, I enter 1 in the column for that year, otherwise blank. I'd like to know the most consecutive events people have attended. So, if I have rows like (with 'b' representing a blank cell): Name1 b 1 1 1 b 1 1 1 1 b 1 b 1, most consecutive = 4 Name2 1 1 b b 1 b b b b 1 b 1 1, most consecutive = 2 Name3 1 1 1 1 1 1 b 1 1 1 1 b 1, most consecutive = 6 Name4 1 1 1 1 1 1 1 1 1 b 1 1 1, most consecutive = 9 Name5 b b b b b b 1 b 1 b 1 b b, most consecutive = 1 Is there a formula I can use to c...

Count by short date and group by agent name
I have a tracking table (tbl_Tracker) that now contains date and time in one field [Date] and agent name in another field [SLR]. I would like run a query to do a count of number of records by agent by day. How do I set up the query to recognize only the date and not the time. I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a record for each date and specific time instead of grouping the whole day. Then I need to build another query that looks at another table (tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a date range and ...

Advice on Install 4.0 please
Hello, I just finished installing a new Server 2008 AD DC for installing MS CRM 4.0. My question is please. Do I need Exchange to do this or can I use another mail server? Also what OU should this application run in? Thank you Joe I double posted this sorry. I made a mistake and posted in the wrong place. CRM does not require Exchange. 4.0's email router can be setup to use any SMTP/POP3 mail server. The OU does not matter either. -- Matt Parks MVP - Dynamics CRM "Joe" <Joe@discussions.microsoft.com> wrote in message news:5CFCA484-C63B-415F-80BD-EC828920CF15@...

Counting Rows
Hello, I am trying to do the following: 1. Count populated Rows using VB 2. If the # of populated rows is less than 2 post a message box dsiplaying the count 3. If the # of populated rows is greater than 3, continue on with the rest of the macro. Anything you can do to hlep is greatly appreciated. This will look at the last populated cell in the desired row. Sub countrowsincolA() mc = 2 '"a" x = Cells(Rows.Count, mc).End(xlUp).Row If x < 2 Then MsgBox "Only " & x Else 'goon MsgBox "oh boy" End If End Sub -- Don Guillett Microsoft MVP Excel S...

Filter Count question
I have the following code: With Me .Filter = "[Status] = 'Open' AND [Source] <> 'SE Meeting'" .FilterOn = True Me.Label32.Caption = "PCCB Action Items" .OrderBy = "[Group_Name], [IssueID]" .OrderByOn = True End With When I execute this on a commandbutton it returns all the open action items not equal to SE Meeting as the source code. That works perfectly. I have a new unbound textbox called AI_Open. Once I execute the above code, i want some code that will count the number of open action i...

Column Headers #4
My column headers have changed to numbers instead of letters, and Im finding it impossible to check my formulas I cant find how to change it back Anybody know??? Kevin, Go to the Tools menu, choose Options, then the General tab. There, uncheck the "R1C1 reference style" setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kevin Depree" <anonymous@discussions.microsoft.com> wrote in message news:0A37C342-500D-445E-816C-7118DB0F8554@microsoft.com... > My column headers have changed to numbers instead o...

Trying to freeze 1 column; keeps freezing 4 columns !?!
I am trying to freeze my "A" column. I select the "A" column, the click Windows/Freeze and it freezes columns A-D! What am I doing wrong? TIA, Larry Woods Click on Col B and then do Freeze Panes. It will freeze everything to the left and above what is selected, so if you only selected a single cell, say B2, it will freeze column A and row 1 as well. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Attitude ...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Outlook express identities #4
I deleted an account from Outlook express because the "identities" were not "managing" properly, now I need an email that was in a folder in that account. Is it gone ferever? Help! Rip Ask in an Outlook Express forum. This is an Microsoft Office Outlook forum. Outlook Express is family of Internet Explorer and Outlook of the Office family. Here is the link for the right forum http://communities.microsoft.com/newsgroups/default.asp?icp=InternetExplorer Good Luck! -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Creat...

CRM 4.0
Hi All - Im receiving a "Server was unable to process request." error when the following line of code executes: Dim accounts As BusinessEntityCollection = server.RetrieveMultiple(query) Any idea on why this occurs. The error is very vague, and does not contain an inner exception. Thanks for shedding light on this if you can. Larry B. Try checking the Details property on the SoapException this will give you the actual error. Otherwise turn on tracing on CRM this will log the same information. -- Patrick Verbeeten (MCPD) Lead Developer Aviva IT Tools for CRM Developers and...

Importing data #4
I'm building a datbase using Filemaker Pro for Macs. I can import data from an Excel spreadsheet into the datbase pretty seamlessly. I would like to send out an excel template and have people fill in the needed data (names, phone numbers etc.) I would like to merge all this info into one excel spreadsheet and then import it into my database. My question is what is the easiest way to merge all the separate excel documents (@400) into one document. Thanks ATC18 -- ATC18 ------------------------------------------------------------------------ ATC18's Profile: http://www.excelfo...

Printing columns #4
I was sent a spreadsheet that is only 38 rows long, but goes from column A to DA. Is there a way to "stack" the columns to print? For example, pon page one I want column a thru e stacked on top of columns f thru j stacked on top of k thru o and so on....... ...

CRM 4 Phone Call Report
I am putting together a report on Phone Calls. I would like for the report to show the owner, activity status, modified on, account and notes associated with the phone call. I have figured out everything except the Account portion. When I created it orginally, I set the column properties as Regarding (Account). This however, only showed the Account if the phone calls regarding field was regarding an account. Some phone calls are regarding a marketing list, campaign, etc. I would think the way to do it would to have the column set to show the recipient, but I can not find this opt...

Dynamics CRM 4.0, how to undo (or revert) customization?
We made a bunch of customizations to the Opportunity entity forms & views, based on a poor understanding of how this entity is to be used. I would now like to put it back to the way it was when the software was installed. Any way to do this without reinstalling? We have a lot of other customizations and data we want to preserve. Thanks for any help. Did you do an export of the 'vanilla' customizations after the initial install? "Ed Cayce" <edgarecayce@gmail.com> wrote in message news:a377de94-089e-4ac0-9a9e-2b629d84661a@a5g2000pre.googlegroups.com... > We...