Hi, I'm attempting to redesign our staff absence spreadsheet. In column A I have a list of staff names. These could be in any order and can be duplicated. In column I is listed the first date of absence. In column J is listed the last date of absence. On another (output) worksheet I'm attempting to create a calculator that will give certain stats for a given period. So the user enters a start date in A10 and an end date in B10. This should give them the absense stats for that period. So in A13 is the number of staff that have been absent in this period - this is the formula I'm having trouble with.

So, for example on worksheet 1(Absence Data):

A I J

NAME 1st date absent Last date absent

Joe Bloggs 1/1/10 5/1/10

Stephen Bloggs 5/2/10 19/2/10

Sinead Jones 3/8/10 5/9/10

Rosy Smyth 6/9/10 8/9/10

Joe Bloggs 9/9/10 12/9/10

Mike Mad 10/9/10 13/9/10

Then on worksheet 2(Output):

A B

Enter Start Date Enter End Date

1/8/10 30/8/10

Total Staff Absent during Period

This is where I'm stuck. This answer here should be = 1 (Sinead Jones) as nobody else was off during Aug.

I'd really appreciate any help on this,

thanks

guy

So, for example on worksheet 1(Absence Data):

A I J

NAME 1st date absent Last date absent

Joe Bloggs 1/1/10 5/1/10

Stephen Bloggs 5/2/10 19/2/10

Sinead Jones 3/8/10 5/9/10

Rosy Smyth 6/9/10 8/9/10

Joe Bloggs 9/9/10 12/9/10

Mike Mad 10/9/10 13/9/10

Then on worksheet 2(Output):

A B

Enter Start Date Enter End Date

1/8/10 30/8/10

Total Staff Absent during Period

This is where I'm stuck. This answer here should be = 1 (Sinead Jones) as nobody else was off during Aug.

I'd really appreciate any help on this,

thanks

guy

- Counting unique entries for a specific date period in a series of dates
- Count Unique Entries Within Variable Date Range
- Using SUMPRODUCT to count unique numbers within a date range
- "COUNTU" function in Excel to count unique entries in a range
- Counting unique entries in column A but only if specific values appear in columns B and C
- &quot;COUNTU&quot; function in Excel to count unique entries in a range
- Counting unique entries in column A but only if specific values appear in columns B and C
- Count Unique Entries in a range.
- Counting Unique Entries
- Start dates, end dates and # of Days
- Pivot Table: Counting Unique Values in a row, and grouping start and end dates
- Pull unique data within a date range that matches criteria...confused.
- Start Date, End Date - fill in dates in between
- COUNTING Unique values within a specified date range
- Count unique combinations within date range
- Count months away betwwen a strat and end date by month
- Counting days within a given date range
- Counting the number of Mondays between a start and end date
- Counting unique entries in a list
- Count unique entries in one column where a criteria is met in another column
- Counting Unique Values with one criteria between a given date range?
- Count Unique records within a group
- Counting Unique entries with a condition
- Counting unique items based on date

Hi, I am looking for a formula to count the number of unique entries within a specific date per month peroid.eg

01.05.09 log 1

05.05.09 log 2

20.05.09 log 1

05.06.09 log 1

06.06.09 log 3

07.08.09 log 1

09.08.09 log 1

10.08.09 log 1

may contains 2 x log 1 and 1 x log 2

june contains 1 x log 1 and 1 x log 2

aug contains 3 x log 3

formula would be how many log 1 entries are contained between 01.05.09 and 30.05.09 answer would be 2.

@countif(a1:a20,"log1", between 01.05.09 and 30.05.09)

So it searches through the series of dates and when when it come to between 01.05.09 and 30.05.09 if counts the number of "log1" entries. and gives a value of 2.

Hard to explain, but can anyone help .

The data is contained in 2 colums, i.e the date is in colum A:1 and the data is contained in B:1.

cheers

Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.

Goal: create a count of unique entries within a defined variable date range

I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.

I've been attempting to use the DCOUNT function but I'm not getting the correct result.

Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.

It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?

As always, your help never lacks my greatest appreciation.

I used

=SUMPRODUCT((B5:B317<>"")/(COUNTIF(B5:B317,B5:B317)+(B5:B317="")))

to count the unique numbers throughout the entire range. But what I would also like to do is narow it down by month.

I also used

=SUMPRODUCT(--(D5:D317="Approved"),--(A5:A317>DATE(2004/12/31)),--(A5:A317<DATE(2005/2/0)))

To count the number of "approved" numbers for a month.

I would like to use part of each formula to create a new formula that would give me the unique numbers within a date range.

Any help would be greatly appreciated.

complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is

required in Excel.

I suggest adding a COUNTU worksheet function that would automatically count

the number of unique data entries. It should have options for counting

numbers, numbers + text, excluding blank cells, etc.

Thanks,

Wayne

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I've found recently a very useful formula on Chip Pearson's page

(http://cpearson.com/excel/duplicat.htm) dealing with "counting unique

entries in a range". I think the best one for my special case would be the

one for "no text / no string" values, i.e.

=SUM(N(FREQUENCY(Range, Range)>0))

From this point, I'm trying to elaborate a bit on this by adding two

conditions in order to get something like:

"Count unique entries in a specific (f. ex. A) column, but only if in column

B we have a value = 555 (number) and in column C we have a value = XYZ

(text)."

I would like to avoid "filter, copy and paste" solution...

Till this point, I was unable to find a correct solution by myself. At one

point I've thought about passing through SUMPRODUCT, but with no success.

Maybe one of you have already had such a problem? Do you see how to resolve

this?

Thanks a lot for any comment or hint!

Mark

complicated nested set of functions (SUM, IF, FREQUENCY, MATCH, LEN, etc.) is

required in Excel.

I suggest adding a COUNTU worksheet function that would automatically count

the number of unique data entries. It should have options for counting

numbers, numbers + text, excluding blank cells, etc.

Thanks,

Wayne

----------------

This post is a suggestion for Microsoft, and Microsoft responds to the

suggestions with the most votes. To vote for this suggestion, click the "I

Agree" button in the message pane. If you do not see the button, follow this

link to open the suggestion in the Microsoft Web-based Newsreader and then

click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

I've found recently a very useful formula on Chip Pearson's page

(http://cpearson.com/excel/duplicat.htm) dealing with "counting unique

entries in a range". I think the best one for my special case would be the

one for "no text / no string" values, i.e.

=SUM(N(FREQUENCY(Range, Range)>0))

From this point, I'm trying to elaborate a bit on this by adding two

conditions in order to get something like:

"Count unique entries in a specific (f. ex. A) column, but only if in column

B we have a value = 555 (number) and in column C we have a value = XYZ

(text)."

I would like to avoid "filter, copy and paste" solution...

Till this point, I was unable to find a correct solution by myself. At one

point I've thought about passing through SUMPRODUCT, but with no success.

Maybe one of you have already had such a problem? Do you see how to resolve

this?

Thanks a lot for any comment or hint!

Mark

count unique entries in the Range A1:A10

i have data which repeats but i ant to count only unique entries???

3 4

2 4

4 7

2 1

1 0

I want the result to be 5 as the number of unique entries. Can this be done?

Thanks

Problem: I want to make it so that there are three columns where a Start Date, End Date, and number of days can be entered.

Depending on which column the user chooses to place input (minimum 2 input values), I want the other (3rd) value to be calculated. I know how to do each individually: If the number of work days are provided, I simply add that value to the start date. If the end date is provided, I use the networkdays function to determine the number of workdays. My problem is that I need to make it user friendly for someone using the template.

I want the user to choose the input (maybe with a drop-down menu?) and have the other value calculated.

Thanks for your help!

I hope I have posted this in the correct section. I am not Excel savvy, i can do the basic formula but I am struggling with a pivot table.

I am using Excel 2007 and the main table is the following:

Customer/Project ID / Tech name / Start date / End date / Months used / Hours / Average hours Per Month

The table holds the data of techs work on projects, were a project can have many enginners and a engineer can be working on many projects. (I have attached a example workbook of this).

I have created a pivot table, however i want to show each month, and with each month it counts how many open projects there are (only include unique values for that month) and how many techs are it.

I can only get it to show months that were either opened or closed, so example: if in Feb, 2 projects were running but 0 was opened or closed, i cannot show Feb on the pivot table.

I need to show the unique count for project and tech.

Can anyone help?

I've created a workbook that functions as a log to record issues that I've had with companies I work with. Generally speaking it is 3 tabs:The data page, which I call the "Issue log".A lists page, for validation lists "Validation Lists".A issue summary page, formatted for the external companies to see what I've logged, called "Company Summary".

My concept was to enter data into the log, and then I've set up my company summary page so that outside of the print area I select the company I want the summary to be for, and the date range (starting date & ending date) for the summary. Then (theoretically) the summary would auto-populate with each of the individual issues that I've entered into the log that match those criteria (company & date range).

My problems are:I can get it to pull the reference numbers for the right company from the first occurance of the company's name to the last, but if an issue is in the middle of that data out of order, it includes that issue as well.When I try to sort the data to fix that problem, it changes the reference number (because it's counted based on order of entry), and in some cases the other company's already have that reference number.

I've created my first column in the log as a concatenate that is formatted as "2AA1", where '2' is the total sequencial count for the log, "AA" would be a 2 letter abbreviation for the company in question, and "1" is the issue count just for that company. The log essentially looks like this:

1AA1 - 6/1/2011 - Issue notes 2BB1 - 6/2/2011 - Issue notes 3AA2 - 6/15/2011 - Issue notes 4CC1 - 6/10/2011 - Issue notesSo basically on the next tab I would select something like:

Builder - *AA*

Start Date - *6/1/2011*

End Date - *6/31/2011*

And I'd like it to pull the specific reference numbers in that range that match my criteria so I can vlookup the other data I need to populate the rest of the form.

I've tried SUMPRODUCT, VLOOKUP, arrays, Pivot Tables, INDEX/MATCH, SUMIF, COUNTIF, etc. I have literally spent the last 12 hours consecutively trying to figure this out on my own, and I just can't make it work. I know one of those methods (or more) can work, I just don't have the skills to do it.

Does anyone have any advice for me? I really appreciate it.

Thanks so much...

-LawC

I have a spreadsheet that counts the number of working days between a date period. I enter a start date and end date, and using the NETWORKDAYS function I am able to return the number of working days during this period.

In addition to the start date and end date I have a column that gives a unique identifier to the start date and end date using the LEFT function and "&" and the date number.

For example, start date is 30 May 2012 = May30 - end date 11 June 2012 =Jun11

What I need to do is fill in the dates between May30 and Jun11 on my unique identifier column, but also only include the working days as counted by NETWORKDAYS.

I hope this makes sense. Please feel free to ask for further clarification if not clear.

Thanks!

KP

I have the following formula

=COUNTIF(Sheet1!N4:N200,">="&TODAY()-14)-COUNTIF(Sheet1!N4:N200,">="&TODAY()-7)

Column is date, and this will look at date ranges between 7 and 14 days before today.

I have a second column which has reference numbers in

So for example

N O

11/11/2010 425635

08/11/2010 325686

etc

What I would like to do is count the unique values in O in the above formula date range.

Can this be done?

Regards

Alex

i am struggling so hard with excel VBA,

i am trying to write a code that counts unique combinations within date range that the user enters from a VBA form (frmSummary) ,,

i have the Visits sheet :

BOOKINGID date clientID petID

1 22/10/2010 1 1

2 23/11/2010 1 2

3 24/11/2010 3 2

4 25/12/2010 2 1

5 26/12/2010 1 1

the pet id is based on the client id,,each client has pets

i want to count the number of pets that visited the clinic,,,( unique combination of clienID and petId)

within a date range that user specified from text box(txtStartDate, txtEndDate)

the count result will be displayed in (summary) worksheet in cell "B6"

am sooooo depressed with this pleeease heeelp me!

thaanx

that counts by Year then Places the by month

Start Date End Date

10/23/06 11/04/06

10/23/06 11/04/06

10/23/06 11/04/06

05/07/06 11/07/06

04/30/06 11/15/06

04/30/06 11/20/06

05/28/06 11/29/06

04/30/06 11/30/06

06/18/06 12/14/06

03/20/06 12/15/06

04/30/06 12/15/06

07/02/06 12/28/06

07/16/06 02/08/07

09/10/06 03/09/07

04/30/06 04/30/07

04/30/06 04/30/07

05/07/06 05/07/07

What I want the result to look like:

(i.e. The calulation result of the first row Would Be 1 For Oct and 1 For

Nov.

After Caluculating the secon row the result would be 2 for Oct and 2 Nov.

CY-06 CY-07 Deployed by month

6 16 JANUARY

5 15 FEBURARY

4 14 MARCH

9 13 APRIL

12 11 MAY

13 6 JUNE

13 3 JULY

13 3 AUGUST

15 2 SEPTEMBER

17 1 OCTOBER

21 NOVEMBER

17 DECEMBER

Thanks in advance for the assist.

I have a start date and an end date which may be any date from start 2001 to end 2005. These date ranges signify durations of projects.

Now, I want to find out how much money I made on these projects between 1/7/04 and 30/6/5.

I know how to work out the price per day. I know how to count the total number of days for each project. What I can't figure out is how to count the number of days during the period that I'm interested in.

Any suggestions?

Many thanks.

Zazie.

I had a formula that uses a start and end date and the excel value for Monday (2) to return the number of mondays there are between two dates.

For the life of me, I can't find it now - anyone have this?

Cheers

Phil

I was wondering if someone could help me. I am trying to write a script that

goes parses through an excel file that contain iventory information. I

already wrote code to cover most of the functionality I am trying to do, but

I am having some difficulty with respect to two areas.

1) The lists vary in length so some 10 records others have 500 records. Is

there an easy way to do a loop to the last element in the list? Like... for

n=1 to sheet.end or something like that? (I do not want to manually change

the bounds for each list.)

2) I am also finding it difficult to automatically generate the list of

unique entries in the script. Currently, I am doing it manually (which is a

really pain). I am trying to get a list of unique items so later on I can

use this list to count their respective quantities.

So if my list is like:

Item Qty

boxes 5

paper 6

paper 1

pens 2

boxes 2

It would return:

boxes

paper

pens

So I can do the counts later.

I greatly appreciate any assistance and/or suggestion you could provide.

Thanks,

-Michael

I wonder if anyone could please assist me as I am completely stuck.

I have a worksheet of data with 6 different columns.

One of the things I wish to do is to count all the unique entries of column C when column E has a particular value.

The trouble is column C had many repeated entries - I wish to count for a particular category in column E the number of unique entries in column C.

I hope that makes sense, I've been pondering this for ages and I think I'm starting to muddle myself now. I can count using criteria, but to only count unique entries with criteria is proving a bit too tricky for me.

Many thanks for any help anyone can give.

This is another unique frequency question also with two conditions. However the difference with my situation is that mine seems to combine two of the common problems, both of which I have solved independently, but have not been able to make work in conjunction.

I need to count unique entries in a list of numbers that fall between a given date range, with one criteria. I have mocked up a sample of my data to explain.

Sample Spreadsheet Data:

ORDER# PROMOCODE

123456 B100

987654 B100

555555 B100

444444 B100

111111 C700

222222 C700

999999 J300

777777 J300

666666 J300

Should Yield:

4 B100

2 C700

3 J300

Can anyone help me figure this out?

Thanks,

I want to count the number of unique entries in column E, based on a condition in column A

In Column A I have the names of 10 Area Managers. In column E I have a list of towns containing duplicates

So I want to know how many unique towns area manager 1 visits etc

Thanks

Paul

date, and column B contains a department. The contents of the column A

(date) are either a simple date "dd/mm/yyyy" format or a timestamp e.g.

"dd/mm/yyyy hh:mm:ss". Duplicates are allowed in Column A, Column B, Column

A + Column B.

I needed (ideally) a worksheet function that would allow me to obtain the

following information:

Based on a START DATE, END DATE and DEPT NAME return to me the number of

times I have the DEPT NAME occuring in my data for between the START DATE and

END DATE (both dates included) but counting multiple rows for the same date

as only 1 record. Thus if I have 27 rows for the same department with date

July 15, then it should give me only the value 1.

The time-part in the timestamp format can be ignored. It exists because of

input data coming from various sources and some sources store time and some

do not store time.

How could I do the above with a worksheet function?

Many thanks.

No luck finding an answer? You could always try Google.