Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Count unique entries within a start date end date range

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


Post your answer or comment

comments powered by Disqus
Hi,

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

First, I feel I should apologize for posting on a topic that is already one of the most frequently asked questions on the forum. However, I've not been able to clear this up despite the simplicity of the task.

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 am trying to count unique numbers within a date range. Where column A containes a list of dates and column B contains a list of numbers.

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.

In order to count the number of unique entries in a range of cells, a very
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

Hello everybody,

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

In order to count the number of unique entries in a range of cells, a very
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

Hello everybody,

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

Is there anyway i can
count unique entries in the Range A1:A10
i have data which repeats but i ant to count only unique entries???

I am trying to count unique entries across a set of colums. For example,

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

Preface: I am working on a template scheduling file for construction management. I have Suretrack and know about Microsoft Project but I want a something specific (functionally and aesthetically) and simple (once it is locked).

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!

Hello All,

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?

Hello all, I'm a new visitor to these forums, I'm here because I'm stuck trying to solve an excel problem.

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 notes
So 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

Hi,

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

Hi This is my first post so please go easy on me

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

hi guys
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

I have two columns (Start date) (End Date). I am trying to create a formula
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.

Hi all,

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.

Hi,

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

Hey Guys!

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

Hello there,
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.

So this is my first post on this forum, mostly because I have spent months lurking and pulling code from other posts. (That's how it should be done on a forum, right ) But I have finally gotten to a problem I haven't been able to solve myself.

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.

My spreadsheet (about 24,000 rows) contains order numbers and promotion codes. I need to count the unique orders within each promo code and show just the PromoCode with the totals. The Sub-Total function doesn't seem to allow for "Count Unique records". I tried using an "Array" formula but I need to adjust it for each group and even then the PromoCode doesn't appear on the same line. I would like to avoid using a Pivot Table.

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 am using excel 2000

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

I have a sheet, with each row containing one record. Column A contains a
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.