Counting if settings are "Final", or "Draft"

Hello Good Samaritans,

I have a spreadsheet which has a list of documents in it. Some are
classified as "Draft" and some are "Final". The classification is always
stored in Column B. I want to extract the number of "Draft" and "Final"
each week (ending on Friday) . The problem is COUNTIF doesn't do this
without a unique formula for each week. Is there another function that can
be used?

Here's a sample of my spreadsheet:

A1 S:/Tax1Volume/Forms_03/01_Sep_03
A2 Draft Doc A
A3 S:/Tax1Volume/Forms_03/02_Sep_10
A4 Draft Doc B
A5 S:/Tax1Volume/Forms_03/03_Sep_17
A6 Draft Doc C
A7 Draft Doc D
A8 S:/Tax1Volume/Forms_03/04_Sep_23
A9 Draft Doc E
A10 Draft Doc F
A11 Final Doc C
A12 Draft Doc G
A13 S:/Tax1Volume/Forms_03/05_Sep_27
A14 Draft Doc H
A15 Final Doc B
A16 S:/Tax1Volume/Forms_03/05_Sep_28
A17 Final Doc A
A18 Draft Doc I
A19 S:/Tax1Volume/Forms_03/07_Sep_30
A20 Final Doc G



I want to get a count of the Draft and Final forms for each week (ending on
Friday), the problem is the forms come in almost daily and they can be
either drafts or finals and there could be 1, or 2 or 625 of them. How do I
write this formula?


Thank you for your help,

Art.

First, I think I'd try to put that date like string on every row. Then I could
use that to in formulas/pivottables/subtotals.

It looked like the drive/folder name was in column C. If that's not true, then
change this formula to point at the correct address:

In cell D1:
=MID(C1,LOOKUP(2,1/(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)="/"),
ROW(INDIRECT("1:"&LEN(C1))))+1,255)
(all one cell)

Also, change that "/" to "\" if your data contained backslashes and not the
slashes you showed.

In, D2, put this:

=IF(OR(B2={"draft","final"}),D1,
MID(C2,LOOKUP(2,1/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)="/"),
ROW(INDIRECT("1:"&LEN(C2))))+1,255))
(all one cell).
And drag down.

Now in E1, type this formula:
=IF(OR(B1={"draft","final"}),"KeepIt","HideIt")
and drag down

Now your will look like:
A B C D E
-- ----- -------------------------------- --------- ------
a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
a2 draft Doc A 01_Sep_03 KeepIt
a3 final Doc B 01_Sep_03 KeepIt
a4 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
a5 Draft Doc C 01_Sep_03 KeepIt
a6 S:/Tax1Volume/Forms_03/02_Sep_10 02_Sep_10 HideIt
a7 Draft Doc D 02_Sep_10 KeepIt
a8 S:/Tax1Volume/Forms_03/03_Sep_17 03_Sep_17 HideIt
a9 Draft Doc F 03_Sep_17 KeepIt
a10 Draft Doc G 03_Sep_17 KeepIt
a11 S:/Tax1Volume/Forms_03/04_Sep_23 04_Sep_23 HideIt
a12 Draft Doc H 04_Sep_23 KeepIt
a13 Draft Doc I 04_Sep_23 KeepIt
a14 Final Doc A 04_Sep_23 KeepIt

Now insert a new row 1 and add nice headers.

A B C D E
-- ----- -------------------------------- --------- ---------
xx Type Folder/Document Name Week# Keep/Hide
a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
a2 draft Doc A 01_Sep_03 KeepIt
a3 final Doc B 01_Sep_03 KeepIt
....

Now select your range A1:E###
and do Data|Pivottable

Since your range is already selected, just click on Next until you get to a
dialog that has a Layout Button on the bottom left corner.

Click that layout button.

Drag the keep/hide button to the page field
drag the Week# button to the row field
drag the type button to the column field
drag the type button (again) to the data field. It should say "Count of type"

Finish up the pivottable wizard by clicking ok.

Now click on the page field dropdown. It should look like "(all)".
Select "keepit"

And click ok.

Tada!

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

====
Later if you have to add more rows to your data, you'll want to make it so that
you can just right click on the pivottable and select refresh.

But for that to happen, the pivottable needs to know what range to use. You can
either go through the wizard again, or you could use a dynamic name that expands
or contracts when you add/delete rows.

You can read more about these dynamic range names at Debra Dalgleish's site:
http://www.contextures.com/xlNames01.html#Dynamic




Art MacNeil wrote:
>
> Hello Good Samaritans,
>
> I have a spreadsheet which has a list of documents in it. Some are
> classified as "Draft" and some are "Final". The classification is always
> stored in Column B. I want to extract the number of "Draft" and "Final"
> each week (ending on Friday) . The problem is COUNTIF doesn't do this
> without a unique formula for each week. Is there another function that can
> be used?
>
> Here's a sample of my spreadsheet:
>
> A1 S:/Tax1Volume/Forms_03/01_Sep_03
> A2 Draft Doc A
> A3 S:/Tax1Volume/Forms_03/02_Sep_10
> A4 Draft Doc B
> A5 S:/Tax1Volume/Forms_03/03_Sep_17
> A6 Draft Doc C
> A7 Draft Doc D
> A8 S:/Tax1Volume/Forms_03/04_Sep_23
> A9 Draft Doc E
> A10 Draft Doc F
> A11 Final Doc C
> A12 Draft Doc G
> A13 S:/Tax1Volume/Forms_03/05_Sep_27
> A14 Draft Doc H
> A15 Final Doc B
> A16 S:/Tax1Volume/Forms_03/05_Sep_28
> A17 Final Doc A
> A18 Draft Doc I
> A19 S:/Tax1Volume/Forms_03/07_Sep_30
> A20 Final Doc G
>
> I want to get a count of the Draft and Final forms for each week (ending on
> Friday), the problem is the forms come in almost daily and they can be
> either drafts or finals and there could be 1, or 2 or 625 of them. How do I
> write this formula?
>
> Thank you for your help,
>
> Art.

--

Dave Peterson

Dave,

I can hardly believe it. It's better than I had imagined. Much better!!
You have my undying gratitude and respect.

Thank you,

Art MacNeil






"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41A88FF9.D0705D00@netscapeXSPAM.com...
> First, I think I'd try to put that date like string on every row. Then I
> could
> use that to in formulas/pivottables/subtotals.
>
> It looked like the drive/folder name was in column C. If that's not true,
> then
> change this formula to point at the correct address:
>
> In cell D1:
> =MID(C1,LOOKUP(2,1/(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)="/"),
> ROW(INDIRECT("1:"&LEN(C1))))+1,255)
> (all one cell)
>
> Also, change that "/" to "\" if your data contained backslashes and not
> the
> slashes you showed.
>
> In, D2, put this:
>
> =IF(OR(B2={"draft","final"}),D1,
> MID(C2,LOOKUP(2,1/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)="/"),
> ROW(INDIRECT("1:"&LEN(C2))))+1,255))
> (all one cell).
> And drag down.
>
> Now in E1, type this formula:
> =IF(OR(B1={"draft","final"}),"KeepIt","HideIt")
> and drag down
>
> Now your will look like:
> A B C D E
> -- ----- -------------------------------- --------- ------
> a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> a2 draft Doc A 01_Sep_03 KeepIt
> a3 final Doc B 01_Sep_03 KeepIt
> a4 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> a5 Draft Doc C 01_Sep_03 KeepIt
> a6 S:/Tax1Volume/Forms_03/02_Sep_10 02_Sep_10 HideIt
> a7 Draft Doc D 02_Sep_10 KeepIt
> a8 S:/Tax1Volume/Forms_03/03_Sep_17 03_Sep_17 HideIt
> a9 Draft Doc F 03_Sep_17 KeepIt
> a10 Draft Doc G 03_Sep_17 KeepIt
> a11 S:/Tax1Volume/Forms_03/04_Sep_23 04_Sep_23 HideIt
> a12 Draft Doc H 04_Sep_23 KeepIt
> a13 Draft Doc I 04_Sep_23 KeepIt
> a14 Final Doc A 04_Sep_23 KeepIt
>
> Now insert a new row 1 and add nice headers.
>
> A B C D E
> -- ----- -------------------------------- --------- ---------
> xx Type Folder/Document Name Week# Keep/Hide
> a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> a2 draft Doc A 01_Sep_03 KeepIt
> a3 final Doc B 01_Sep_03 KeepIt
> ...
>
> Now select your range A1:E###
> and do Data|Pivottable
>
> Since your range is already selected, just click on Next until you get to
> a
> dialog that has a Layout Button on the bottom left corner.
>
> Click that layout button.
>
> Drag the keep/hide button to the page field
> drag the Week# button to the row field
> drag the type button to the column field
> drag the type button (again) to the data field. It should say "Count of
> type"
>
> Finish up the pivottable wizard by clicking ok.
>
> Now click on the page field dropdown. It should look like "(all)".
> Select "keepit"
>
> And click ok.
>
> Tada!
>
> If you want to read more about the pivottable stuff, you may want to look
> at
> some links:
>
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://peltiertech.com/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
>
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistan...lconPT101.aspx
>
> ====
> Later if you have to add more rows to your data, you'll want to make it so
> that
> you can just right click on the pivottable and select refresh.
>
> But for that to happen, the pivottable needs to know what range to use.
> You can
> either go through the wizard again, or you could use a dynamic name that
> expands
> or contracts when you add/delete rows.
>
> You can read more about these dynamic range names at Debra Dalgleish's
> site:
> http://www.contextures.com/xlNames01.html#Dynamic
>
>
>
>
> Art MacNeil wrote:
>>
>> Hello Good Samaritans,
>>
>> I have a spreadsheet which has a list of documents in it. Some are
>> classified as "Draft" and some are "Final". The classification is always
>> stored in Column B. I want to extract the number of "Draft" and "Final"
>> each week (ending on Friday) . The problem is COUNTIF doesn't do this
>> without a unique formula for each week. Is there another function that
>> can
>> be used?
>>
>> Here's a sample of my spreadsheet:
>>
>> A1 S:/Tax1Volume/Forms_03/01_Sep_03
>> A2 Draft Doc A
>> A3 S:/Tax1Volume/Forms_03/02_Sep_10
>> A4 Draft Doc B
>> A5 S:/Tax1Volume/Forms_03/03_Sep_17
>> A6 Draft Doc C
>> A7 Draft Doc D
>> A8 S:/Tax1Volume/Forms_03/04_Sep_23
>> A9 Draft Doc E
>> A10 Draft Doc F
>> A11 Final Doc C
>> A12 Draft Doc G
>> A13 S:/Tax1Volume/Forms_03/05_Sep_27
>> A14 Draft Doc H
>> A15 Final Doc B
>> A16 S:/Tax1Volume/Forms_03/05_Sep_28
>> A17 Final Doc A
>> A18 Draft Doc I
>> A19 S:/Tax1Volume/Forms_03/07_Sep_30
>> A20 Final Doc G
>>
>> I want to get a count of the Draft and Final forms for each week (ending
>> on
>> Friday), the problem is the forms come in almost daily and they can be
>> either drafts or finals and there could be 1, or 2 or 625 of them. How
>> do I
>> write this formula?
>>
>> Thank you for your help,
>>
>> Art.
>
> --
>
> Dave Peterson

If you really like and have to update that data, remember to take a look at
Debra's dynamic range page. It'll make life simpler after your changes.

Art MacNeil wrote:
>
> Dave,
>
> I can hardly believe it. It's better than I had imagined. Much better!!
> You have my undying gratitude and respect.
>
> Thank you,
>
> Art MacNeil
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> news:41A88FF9.D0705D00@netscapeXSPAM.com...
> > First, I think I'd try to put that date like string on every row. Then I
> > could
> > use that to in formulas/pivottables/subtotals.
> >
> > It looked like the drive/folder name was in column C. If that's not true,
> > then
> > change this formula to point at the correct address:
> >
> > In cell D1:
> > =MID(C1,LOOKUP(2,1/(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)="/"),
> > ROW(INDIRECT("1:"&LEN(C1))))+1,255)
> > (all one cell)
> >
> > Also, change that "/" to "\" if your data contained backslashes and not
> > the
> > slashes you showed.
> >
> > In, D2, put this:
> >
> > =IF(OR(B2={"draft","final"}),D1,
> > MID(C2,LOOKUP(2,1/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)="/"),
> > ROW(INDIRECT("1:"&LEN(C2))))+1,255))
> > (all one cell).
> > And drag down.
> >
> > Now in E1, type this formula:
> > =IF(OR(B1={"draft","final"}),"KeepIt","HideIt")
> > and drag down
> >
> > Now your will look like:
> > A B C D E
> > -- ----- -------------------------------- --------- ------
> > a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > a2 draft Doc A 01_Sep_03 KeepIt
> > a3 final Doc B 01_Sep_03 KeepIt
> > a4 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > a5 Draft Doc C 01_Sep_03 KeepIt
> > a6 S:/Tax1Volume/Forms_03/02_Sep_10 02_Sep_10 HideIt
> > a7 Draft Doc D 02_Sep_10 KeepIt
> > a8 S:/Tax1Volume/Forms_03/03_Sep_17 03_Sep_17 HideIt
> > a9 Draft Doc F 03_Sep_17 KeepIt
> > a10 Draft Doc G 03_Sep_17 KeepIt
> > a11 S:/Tax1Volume/Forms_03/04_Sep_23 04_Sep_23 HideIt
> > a12 Draft Doc H 04_Sep_23 KeepIt
> > a13 Draft Doc I 04_Sep_23 KeepIt
> > a14 Final Doc A 04_Sep_23 KeepIt
> >
> > Now insert a new row 1 and add nice headers.
> >
> > A B C D E
> > -- ----- -------------------------------- --------- ---------
> > xx Type Folder/Document Name Week# Keep/Hide
> > a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > a2 draft Doc A 01_Sep_03 KeepIt
> > a3 final Doc B 01_Sep_03 KeepIt
> > ...
> >
> > Now select your range A1:E###
> > and do Data|Pivottable
> >
> > Since your range is already selected, just click on Next until you get to
> > a
> > dialog that has a Layout Button on the bottom left corner.
> >
> > Click that layout button.
> >
> > Drag the keep/hide button to the page field
> > drag the Week# button to the row field
> > drag the type button to the column field
> > drag the type button (again) to the data field. It should say "Count of
> > type"
> >
> > Finish up the pivottable wizard by clicking ok.
> >
> > Now click on the page field dropdown. It should look like "(all)".
> > Select "keepit"
> >
> > And click ok.
> >
> > Tada!
> >
> > If you want to read more about the pivottable stuff, you may want to look
> > at
> > some links:
> >
> > Debra Dalgleish's pictures at Jon Peltier's site:
> > http://peltiertech.com/Excel/Pivots/pivottables.htm
> > And Debra's own site:
> > http://www.contextures.com/xlPivot01.html
> >
> > John Walkenbach also has some at:
> > http://j-walk.com/ss/excel/files/general.htm
> > (look for Tony Gwynn's Hit Database)
> >
> > Chip Pearson keeps Harald Staff's notes at:
> > http://www.cpearson.com/excel/pivots.htm
> >
> > MS has some at (xl2000 and xl2002):
> > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> > http://office.microsoft.com/assistan...lconPT101.aspx
> >
> > ====
> > Later if you have to add more rows to your data, you'll want to make it so
> > that
> > you can just right click on the pivottable and select refresh.
> >
> > But for that to happen, the pivottable needs to know what range to use.
> > You can
> > either go through the wizard again, or you could use a dynamic name that
> > expands
> > or contracts when you add/delete rows.
> >
> > You can read more about these dynamic range names at Debra Dalgleish's
> > site:
> > http://www.contextures.com/xlNames01.html#Dynamic
> >
> >
> >
> >
> > Art MacNeil wrote:
> >>
> >> Hello Good Samaritans,
> >>
> >> I have a spreadsheet which has a list of documents in it. Some are
> >> classified as "Draft" and some are "Final". The classification is always
> >> stored in Column B. I want to extract the number of "Draft" and "Final"
> >> each week (ending on Friday) . The problem is COUNTIF doesn't do this
> >> without a unique formula for each week. Is there another function that
> >> can
> >> be used?
> >>
> >> Here's a sample of my spreadsheet:
> >>
> >> A1 S:/Tax1Volume/Forms_03/01_Sep_03
> >> A2 Draft Doc A
> >> A3 S:/Tax1Volume/Forms_03/02_Sep_10
> >> A4 Draft Doc B
> >> A5 S:/Tax1Volume/Forms_03/03_Sep_17
> >> A6 Draft Doc C
> >> A7 Draft Doc D
> >> A8 S:/Tax1Volume/Forms_03/04_Sep_23
> >> A9 Draft Doc E
> >> A10 Draft Doc F
> >> A11 Final Doc C
> >> A12 Draft Doc G
> >> A13 S:/Tax1Volume/Forms_03/05_Sep_27
> >> A14 Draft Doc H
> >> A15 Final Doc B
> >> A16 S:/Tax1Volume/Forms_03/05_Sep_28
> >> A17 Final Doc A
> >> A18 Draft Doc I
> >> A19 S:/Tax1Volume/Forms_03/07_Sep_30
> >> A20 Final Doc G
> >>
> >> I want to get a count of the Draft and Final forms for each week (ending
> >> on
> >> Friday), the problem is the forms come in almost daily and they can be
> >> either drafts or finals and there could be 1, or 2 or 625 of them. How
> >> do I
> >> write this formula?
> >>
> >> Thank you for your help,
> >>
> >> Art.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

hahhaha use Access to report on data like that; it is much easier




"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:41A8FDBE.186504A1@netscapeXSPAM.com...
> If you really like and have to update that data, remember to take a look
at
> Debra's dynamic range page. It'll make life simpler after your changes.
>
> Art MacNeil wrote:
> >
> > Dave,
> >
> > I can hardly believe it. It's better than I had imagined. Much
better!!
> > You have my undying gratitude and respect.
> >
> > Thank you,
> >
> > Art MacNeil
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
> > news:41A88FF9.D0705D00@netscapeXSPAM.com...
> > > First, I think I'd try to put that date like string on every row.
Then I
> > > could
> > > use that to in formulas/pivottables/subtotals.
> > >
> > > It looked like the drive/folder name was in column C. If that's not
true,
> > > then
> > > change this formula to point at the correct address:
> > >
> > > In cell D1:
> > > =MID(C1,LOOKUP(2,1/(MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)="/"),
> > > ROW(INDIRECT("1:"&LEN(C1))))+1,255)
> > > (all one cell)
> > >
> > > Also, change that "/" to "\" if your data contained backslashes and
not
> > > the
> > > slashes you showed.
> > >
> > > In, D2, put this:
> > >
> > > =IF(OR(B2={"draft","final"}),D1,
> > > MID(C2,LOOKUP(2,1/(MID(C2,ROW(INDIRECT("1:"&LEN(C2))),1)="/"),
> > > ROW(INDIRECT("1:"&LEN(C2))))+1,255))
> > > (all one cell).
> > > And drag down.
> > >
> > > Now in E1, type this formula:
> > > =IF(OR(B1={"draft","final"}),"KeepIt","HideIt")
> > > and drag down
> > >
> > > Now your will look like:
> > > A B C D E
> > > -- ----- -------------------------------- --------- ------
> > > a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > > a2 draft Doc A 01_Sep_03 KeepIt
> > > a3 final Doc B 01_Sep_03 KeepIt
> > > a4 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > > a5 Draft Doc C 01_Sep_03 KeepIt
> > > a6 S:/Tax1Volume/Forms_03/02_Sep_10 02_Sep_10 HideIt
> > > a7 Draft Doc D 02_Sep_10 KeepIt
> > > a8 S:/Tax1Volume/Forms_03/03_Sep_17 03_Sep_17 HideIt
> > > a9 Draft Doc F 03_Sep_17 KeepIt
> > > a10 Draft Doc G 03_Sep_17 KeepIt
> > > a11 S:/Tax1Volume/Forms_03/04_Sep_23 04_Sep_23 HideIt
> > > a12 Draft Doc H 04_Sep_23 KeepIt
> > > a13 Draft Doc I 04_Sep_23 KeepIt
> > > a14 Final Doc A 04_Sep_23 KeepIt
> > >
> > > Now insert a new row 1 and add nice headers.
> > >
> > > A B C D E
> > > -- ----- -------------------------------- --------- ---------
> > > xx Type Folder/Document Name Week# Keep/Hide
> > > a1 S:/Tax1Volume/Forms_03/01_Sep_03 01_Sep_03 HideIt
> > > a2 draft Doc A 01_Sep_03 KeepIt
> > > a3 final Doc B 01_Sep_03 KeepIt
> > > ...
> > >
> > > Now select your range A1:E###
> > > and do Data|Pivottable
> > >
> > > Since your range is already selected, just click on Next until you get
to
> > > a
> > > dialog that has a Layout Button on the bottom left corner.
> > >
> > > Click that layout button.
> > >
> > > Drag the keep/hide button to the page field
> > > drag the Week# button to the row field
> > > drag the type button to the column field
> > > drag the type button (again) to the data field. It should say "Count
of
> > > type"
> > >
> > > Finish up the pivottable wizard by clicking ok.
> > >
> > > Now click on the page field dropdown. It should look like "(all)".
> > > Select "keepit"
> > >
> > > And click ok.
> > >
> > > Tada!
> > >
> > > If you want to read more about the pivottable stuff, you may want to
look
> > > at
> > > some links:
> > >
> > > Debra Dalgleish's pictures at Jon Peltier's site:
> > > http://peltiertech.com/Excel/Pivots/pivottables.htm
> > > And Debra's own site:
> > > http://www.contextures.com/xlPivot01.html
> > >
> > > John Walkenbach also has some at:
> > > http://j-walk.com/ss/excel/files/general.htm
> > > (look for Tony Gwynn's Hit Database)
> > >
> > > Chip Pearson keeps Harald Staff's notes at:
> > > http://www.cpearson.com/excel/pivots.htm
> > >
> > > MS has some at (xl2000 and xl2002):
> > > http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> > > http://office.microsoft.com/assistan...lconPT101.aspx
> > >
> > > ====
> > > Later if you have to add more rows to your data, you'll want to make
it so
> > > that
> > > you can just right click on the pivottable and select refresh.
> > >
> > > But for that to happen, the pivottable needs to know what range to
use.
> > > You can
> > > either go through the wizard again, or you could use a dynamic name
that
> > > expands
> > > or contracts when you add/delete rows.
> > >
> > > You can read more about these dynamic range names at Debra Dalgleish's
> > > site:
> > > http://www.contextures.com/xlNames01.html#Dynamic
> > >
> > >
> > >
> > >
> > > Art MacNeil wrote:
> > >>
> > >> Hello Good Samaritans,
> > >>
> > >> I have a spreadsheet which has a list of documents in it. Some are
> > >> classified as "Draft" and some are "Final". The classification is
always
> > >> stored in Column B. I want to extract the number of "Draft" and
"Final"
> > >> each week (ending on Friday) . The problem is COUNTIF doesn't do
this
> > >> without a unique formula for each week. Is there another function
that
> > >> can
> > >> be used?
> > >>
> > >> Here's a sample of my spreadsheet:
> > >>
> > >> A1 S:/Tax1Volume/Forms_03/01_Sep_03
> > >> A2 Draft Doc A
> > >> A3 S:/Tax1Volume/Forms_03/02_Sep_10
> > >> A4 Draft Doc B
> > >> A5 S:/Tax1Volume/Forms_03/03_Sep_17
> > >> A6 Draft Doc C
> > >> A7 Draft Doc D
> > >> A8 S:/Tax1Volume/Forms_03/04_Sep_23
> > >> A9 Draft Doc E
> > >> A10 Draft Doc F
> > >> A11 Final Doc C
> > >> A12 Draft Doc G
> > >> A13 S:/Tax1Volume/Forms_03/05_Sep_27
> > >> A14 Draft Doc H
> > >> A15 Final Doc B
> > >> A16 S:/Tax1Volume/Forms_03/05_Sep_28
> > >> A17 Final Doc A
> > >> A18 Draft Doc I
> > >> A19 S:/Tax1Volume/Forms_03/07_Sep_30
> > >> A20 Final Doc G
> > >>
> > >> I want to get a count of the Draft and Final forms for each week
(ending
> > >> on
> > >> Friday), the problem is the forms come in almost daily and they can
be
> > >> either drafts or finals and there could be 1, or 2 or 625 of them.
How
> > >> do I
> > >> write this formula?
> > >>
> > >> Thank you for your help,
> > >>
> > >> Art.
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson