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

[Art MacNeil]

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]

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/assistance/2002/articles/xlconPT101.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]

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]

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.

 

[Guest]

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