Calculations within summary cells, reflecting the visble cells only after filtering

[Mark]

Hi

Wonder if someone can tell me whether it is possible to have
calculations reflecting only the data of visible cells following a
filtering exercise.
For example, the columns below show data corresponsing to specific
individuals (the name of the individual, initial, first date of
absence, last date of absence, no of days absent and certification
type). Below this data range is a cell with today's date and a cell
with the date 12 months previous to this date.
There is also a cell which reflects the total no of absence periods
within this data range (20) - see calculation below.

Would like to be in a position to filter on a specific individual's
name and the result in cell F27 change to reflect the total number of
absence periods corresponsing to this person.

Column:
A B C D E F

Name Initial First date Last date No of days
Certification
1 Smith M 15/02/2003 24/02/2003 6 m/c
2 Smith M 11/07/2003 15/07/2003 3 s/c
3 Smith M 05/01/2004 09/01/2004 5 s/c
4 Smith M 29/04/2004 29/04/2004 1 s/c
5 Smith M 10/05/2004 15/05/2004 5 s/c
6 Smith M 02/06/2004 04/06/2004 3 s/c
7 Davis C 20/01/2004 20/01/2004 1 s/c
8 Davis C 17/02/2004 18/02/2004 2 s/c
9 Davis P 20/04/2004 29/04/2004 8 m/c
10 Davis C 24/05/2004 24/05/2004 1 s/c
11 Peters J 18/10/2003 24/10/2003 5 s/c
12 Jones R 14/08/2003 15/08/2003 2 s/c
13 Jones R 20/10/2003 20/10/2003 1 s/c
14 Davis C 02/06/2004 02/06/2004 1 s/c
15 Jones R 11/02/2004 12/02/2004 2 s/c
16 Smith M 08/04/2004 08/04/2004 1 s/c
17 Jones R 13/05/2004 14/05/2004 2 s/c
18 White N 09/01/2004 09/01/2004 1 s/c
19 White N 15/02/2004 24/02/2004 7 m/c
20 Peters J 24/11/2003 28/11/2003 5 s/c
21 Peters J 13/02/2004 13/02/2004 1 s/c
22
23
24
25 Todays date 07/07/2004
26 12 months prior to today's date 07/07/2003
27 Total No of periods in last 12 months 20

F27 =SUMPRODUCT(--($C$1:$C$1000<=$F$25),--($C$1:$C$1000>=F26))+SUMPRODUCT(--($C$1:$C$1000<F26),--($D$1:$D$1000>=F26))

Hope this looks clear and data hasn't jumped following posting.
Any help would be appreciated.
Mark

 

[Peo Sjoblom]

Use SUBTOTAL, e.g.

=SUBTOTAL(9,A2:A500)

will sum visible cells when the others are hidden by filtering

look in help for subtotal, you can get average , min, max and so on

From Excel 2003 onwards there are some added things like subtotal for
hidden cells that are hidden by format>column/row>hide

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi
if you only want the number of occurences for the last 12
months try the following:

=SUMPRODUCT(--($C$1:$C$1000<=$F$25),--
($C$1:$C$1000>=F26),SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT
("A1:A1000"))-1,0)))+SUMPRODUCT(--($C$1:$C$1000<F26),--
($D$1:$D$1000>=F26),SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT
("A1:A1000"))-1,0)))

 

[Peo Sjoblom]

Doh! At least I read the first 5 lines of your post, see Frank's answer

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Frank Kabel]

Hi Peo
and I was just wondering why you provided the simple
solution. especially as my solution is based on one of
your older postings <vbg>

Frank

 

[Mark]

Many thanks for your help Frank. Works a treat!

Hi
if you only want the number of occurences for the last 12
months try the following:

=SUMPRODUCT(--($C$1:$C$1000<=$F$25),--
($C$1:$C$1000>=F26),SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT
("A1:A1000"))-1,0)))+SUMPRODUCT(--($C$1:$C$1000<F26),--
($D$1:$D$1000>=F26),SUBTOTAL(3,OFFSET(A1,ROW(INDIRECT
("A1:A1000"))-1,0)))