Wildcard Usage: I absolutely need this to count?/sum? two or more criteri

Number Status Date
N01-02 Draft 4-May-05
N01-02 Draft 4-May-05
N01-92 In Process 15-Oct-05
N01-88 Draft 15-May-05
N01-29 In Process 9-Jul-05
N01-32 In Process 9-Jul-05
N02-22 Draft 24-Aug-05
N02-27 Draft 24-Aug-05
N20-12 Assigned 3-Dec-05
N24-19 Draft 24-Aug-05
N33-13 Doc Written 4-May-07
N44-04 Cancelled 15-Oct-05


My header are Number, Status, and Date (columns A,B and C). The N dcuments represent my rows (A2-A13).

Since I don't know how to write HTML and indent in the is post, let me just say:
So for example, my first spreadsheet entry is: Number=N01-02; Status=Draft; Date=4-May-05

In my real file, I have over 300 documents. The documents are in groups. Let’s say "N01" is a group of like documents about Dogs. The next two numbers “-02” give the document its uniqueness (aside from its Title, which I have not represented in the example for simplicity sake). Let’s say the “-02” part is dealing with green dogs. And if I were to pick on another document. Let’s say N01-92; that would be about red dogs. And N33-13, another group would be about fat birds and so on. Got me so far?

What I need to do is take every document group and find out for example:

How many N01 documents did I finish in the first quarter of the year?

How many N44 documents do I have in draft this quarter, which in the fourth quarter?

Every single status, I have to in some way account for in each document.
At some point, the status of the documents changes as the documents are progressing to different stages. But I want to this information documented. Most likely all of my summing and counting and such will be on a summary page separate from the tab I’ll be counting.

--Please hellllpppp!!!

Originally Posted by Inexcelhell

Number Status Date
N01-02 Draft 4-May-05
N01-02 Draft 4-May-05
N01-92 In Process 15-Oct-05
N01-88 Draft 15-May-05
N01-29 In Process 9-Jul-05
N01-32 In Process 9-Jul-05
N02-22 Draft 24-Aug-05
N02-27 Draft 24-Aug-05
N20-12 Assigned 3-Dec-05
N24-19 Draft 24-Aug-05
N33-13 Doc Written 4-May-07
N44-04 Cancelled 15-Oct-05


My header are Number, Status, and Date (columns A,B and C). The N dcuments represent my rows (A2-A13).

Since I don't know how to write HTML and indent in the is post, let me just say:
So for example, my first spreadsheet entry is: Number=N01-02; Status=Draft; Date=4-May-05

In my real file, I have over 300 documents. The documents are in groups. Let’s say "N01" is a group of like documents about Dogs. The next two numbers “-02” give the document its uniqueness (aside from its Title, which I have not represented in the example for simplicity sake). Let’s say the “-02” part is dealing with green dogs. And if I were to pick on another document. Let’s say N01-92; that would be about red dogs. And N33-13, another group would be about fat birds and so on. Got me so far?

What I need to do is take every document group and find out for example:

How many N01 documents did I finish in the first quarter of the year?

How many N44 documents do I have in draft this quarter, which in the fourth quarter?

Every single status, I have to in some way account for in each document.
At some point, the status of the documents changes as the documents are progressing to different stages. But I want to this information documented. Most likely all of my summing and counting and such will be on a summary page separate from the tab I’ll be counting.

--Please hellllpppp!!!

Hi Inexcelhell

To get counts by Document type you will need a helper, column, I uinserted a column at B and used thiss formula > =LEFT(A2,FIND("-",A2)-1), copy down the 300 rows, the easiest way is to move the cursor to the botom right of the cell you enterred the formula it will change to a black cross double click the formula will be copied to the bottom of the column. Now copy column B and paste special values

To get counts by quarter you will need a column showing the quarter, I've used column E, you can then use the following array formula >

=SUM(($B$2:$B$13="N01")*($C$2:$C$13="Draft")*($E$2:$E$13="Q2"))

enter this in cell where you want the counts, change the ranges to suit and enter as an array using CTRL, SHIFT, ENTER

You can then change the criteria as required