different values count

Hi, guys!!!
need some help...

each day i record one or more record 
at the end of the month I must count the working day of the mount
counting only dates that are different.

that is:

20/09/2004   abcabcabc
20/09/2004   abcabcabc
20/09/2004   abcbabcab
21/09/2004   abcbabcba
22/09/2004   abcababcb
22/09/2004   abababcba
23/09/2004   acbcbabcb

working days = 4 (the 20, 21, 22 and 23)

is there any function that count different values

i'm coming crazy to solve this problem...

thanks to you al

--
ramalin
-----------------------------------------------------------------------
ramalina's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465
View this thread: http://www.excelforum.com/showthread.php?threadid=26274

0
9/23/2004 10:57:59 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
549 Views

Similar Articles

[PageSpeed] 57

Type in =SUM(1/COUNTIF(A1:A10,A1:A10)) and hold down Control+ShiftKey+Enter
afterwards it should look like this    {=SUM(1/COUNTIF(A1:A10,A1:A10))}
HTH

"ramalina" <ramalina.1d18xz@excelforum-nospam.com> wrote in message
news:ramalina.1d18xz@excelforum-nospam.com...
>
> Hi, guys!!!
> need some help...
>
> each day i record one or more record
> at the end of the month I must count the working day of the mount,
> counting only dates that are different.
>
> that is:
>
> 20/09/2004   abcabcabc
> 20/09/2004   abcabcabc
> 20/09/2004   abcbabcab
> 21/09/2004   abcbabcba
> 22/09/2004   abcababcb
> 22/09/2004   abababcba
> 23/09/2004   acbcbabcb
>
> working days = 4 (the 20, 21, 22 and 23)
>
> is there any function that count different values
>
> i'm coming crazy to solve this problem...
>
> thanks to you all
>
>
> -- 
> ramalina
> ------------------------------------------------------------------------
> ramalina's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14653
> View this thread: http://www.excelforum.com/showthread.php?threadid=262746
>


0
jmay (696)
9/23/2004 11:28:24 AM
I am sure there are some clever functions that will do it directly bu
here is a simple method using a helper column

If the dates are in column A then use a column with the formula

=if(a2=a1,"",1)

you can then sum that column to find the number of working days

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=26274

0
9/23/2004 11:35:11 AM
On Thu, 23 Sep 2004 05:57:59 -0500, ramalina
<ramalina.1d18xz@excelforum-nospam.com> wrote:

>
>Hi, guys!!!
>need some help...
>
>each day i record one or more record 
>at the end of the month I must count the working day of the mount,
>counting only dates that are different.
>
>that is:
>
>20/09/2004   abcabcabc
>20/09/2004   abcabcabc
>20/09/2004   abcbabcab
>21/09/2004   abcbabcba
>22/09/2004   abcababcb
>22/09/2004   abababcba
>23/09/2004   acbcbabcb
>
>working days = 4 (the 20, 21, 22 and 23)
>
>is there any function that count different values
>
>i'm coming crazy to solve this problem...
>
>thanks to you all

1. Download Laurent Longre's morefunc.xll and use his COUNTDIFF function.

2. If you don't have too many, and if there are no blanks in the range, you can
use the ARRAY formula:  
	
	=SUM(1/COUNTIF(Dates,Dates))

3. To enter an ARRAY formula, after typing or pasting it into the formula bar,
hold down <ctrl><shift> while hitting <enter>.  XL will place braces {...}
around the formula.
	

4.  Use a Pivot Table.
	Select the column of dates
	Data/Pivot Table/Next/Finish
	Drag the Date element into both the Rows and Data Fields.  This gives
you a Pivot Table on a new sheet.

	In a cell not in Column A, enter the formula:

		=COUNTA(A:A)-3

	The -3 is to subtract the Header and Grand Total lines that using the
defaults in the Pivot Table Wizard will cause.


--ron
0
ronrosenfeld (3122)
9/23/2004 12:05:40 PM
Hi
and another alternative which ignores blank cells:
=SUMPRODUCT((A1:A100<>"")/(COUNTIF(A1:a100,A1:A100)+
(A1:A100="")))

>-----Original Message-----
>
>Hi, guys!!!
>need some help...
>
>each day i record one or more record 
>at the end of the month I must count the working day of 
the mount,
>counting only dates that are different.
>
>that is:
>
>20/09/2004   abcabcabc
>20/09/2004   abcabcabc
>20/09/2004   abcbabcab
>21/09/2004   abcbabcba
>22/09/2004   abcababcb
>22/09/2004   abababcba
>23/09/2004   acbcbabcb
>
>working days = 4 (the 20, 21, 22 and 23)
>
>is there any function that count different values
>
>i'm coming crazy to solve this problem...
>
>thanks to you all
>
>
>-- 
>ramalina
>----------------------------------------------------------
--------------
>ramalina's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=14653
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=262746
>
>.
>
0
frank.kabel (11126)
9/23/2004 12:43:39 PM
Reply:

Similar Artilces:

How to fill fill a column with numbers, beginning at number X, counting up.
I simply need to add numbers, beginning with 15,347, (counting upward), to a column with empty values in a table. Is there an easy way to do this, rather than completing it in excel and importing it, then attempting to update the table? Thanks for any suggestions. While I cannot imagine a legitimate use for this, the following code will insert rows starting at 15347 and going to 22000 Dim dbCurr As DAO.Database Dim lngLoop As Long Dim strSQL As String Set dbCurr = CurrentDb For lngLoop = 15347 To 22000 strSQL = "INSERT INTO MyTable (MyField) " & _ "VALUE...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Count first occurance of text
I have a row of 24 cells, some are blank and others have text. I am trying to create a formula that returns the first instance of text being used. I have tried using a match, but it gives me an error because I am trying to pull text not a number. thanks Here's one dart throw .. Assume your 24 source cells are A2:A25 Place in say, B2, normal ENTER to confirm: =INDEX(A2:A25,MATCH(TRUE,INDEX(ISTEXT(A2:A25),),0)) Bullseye? hit the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Chad Wods...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

merging a value in a text field with each line in a memo field
I am trying to merge a value in a text field with the information in a memo field using a query to create a report. This is the query I am using: SELECT AVRelay+","+IPRange FROM T_Sites AVRelay is the text field and IPRange is the Memo. IPRange has multiple lines, each of which I need appended with the value in AVRelay for the report. It should look like. 2,192.168.1.0/24 2,192.168.0.0/24 2,192.168.2.0/24 Instead it looks like 2,192.168.1.0/24 192.168.0.0/24 192.168.2.0/24 I have searched far and wide but has come up empty. Please help! I would gues...

Returning all values with a join
I have a table that lists business summary data by day. Not every business has information on each day. However I need to pull in the business along with zero values when this happens. I created a table listing all of the business names and did a join, however it is still not pulling in the businesses without any information on that day. Here is my query - any help would be appreciated. SELECT [Outbound Business Names].[Business Name], [Dialer Summary results].RowDate FROM [Dialer Summary results] RIGHT JOIN [Outbound Business Names] ON [ Dialer Summary results].Business = [O...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

Convert String to Numeric Values
I have data string that I must convert to numeric values. Ex: 00100200300400500600700800901000110015016 How do I get the above data string example to return the numeric values that are three digit integers. How do I get the number 16 for example? Any assistance is appreciated -- JenISCM Try this: Public Sub MySplit(txtIn As String) ' copy/paste to a standard module ' input from debug window: ' call mysplit("00100200300400500600700800901000110015016") Dim i As Integer Dim n As Integer i = 1 For n = 1 To CInt(Len(txtIn) / 3) Debug.Print n & " - " ...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...

Counting Specific Text
I would like to be able to count the number of times a particular text entry is listed within a column. For example if there is a list and Apples are in the list 4 times, I would like a formula that would count the number of times apples appeared in colunn A and place that number in column B. Thanks for the help. Todd Todd If you want to summarise more than one type, you may be better off using a pivot table (from Data/Pivot Table). If it's just a one-off use: =COUNTIF(A1:A100,"Apples") Andy. "Todd" <todd@yahoo.com> wrote in message news:060f01c376d7$f...

Want to display value and difference on one chart
I am trying to create a chart that displays x and 10-x on the same chart. I want the chart to show a meaured value and the difference of that value to 10 in a different color. On Wed, 6 Aug 2008, in microsoft.public.excel.charting, JMH <JMH@discussions.microsoft.com> said: >I am trying to create a chart that displays x and 10-x on the same chart. I >want the chart to show a meaured value and the difference of that value to 10 >in a different color. Use your spreadsheet to calculate the values, then use the chart to graph them. Never try to use a chart for calculation. ...

Sending from 2 different computers
I have Outlook 2003 on my desktop at home and 2002 on my laptop while traveling. My server is Comcast. I would strongly prefer to use Outlook (rather than Comcast's webmail) on both computers. I have configured Outlook on both computers to receive email. I can also leave a copy on the server so it can be downloaded again later on the other computer, assuring that I always have copies of all emails no matter which computer I'm using. Problem is I can't send from my laptop. I've tried Comcast's instructions for configuring Outlook 2002 for use while traveling, but it doesn&...

How to solve problem (equal to 0) with unknown value
I'm trying to solve a complex problem with an unknown value that is equal to zero using solver. How do I do this? ...

How can I count characters and then jump the abyss?
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel My form has a single one-row table that takes up the whole page for entry, by a user, of a narrative description of a problem. But the narrative, if it is long, may need to extend to another blank page. How do I automate jumping the abyss? My first idea is to count characters as they are entered (monospace characters) and when the limit is reached, move the insertion to a field in a new table on the next page. The character count would have to include spaces. I am looking for VBA code to do this but haven't fi...

Large variation in series values
Good Morning, I would like to display serie A (in millions) and serie B (in thousands) in the same chart (preferably line). What is the best way to do this; because of the millions in serie A, serie B line gets 'scrunched' to the bottom. Any suggestions? Thanks! Carol In article <A8F065CC-4B74-49A7-A56B-4DE1DFDDBAEA@microsoft.com>, QPapillon@discussions.microsoft.com says... > Good Morning, > > I would like to display serie A (in millions) and serie B (in thousands) in > the same chart (preferably line). What is the best way to do this; because > of th...

Different shape width in different views in Excel 2007??
In Excel 2007, I create a shape in "Normal" view and set its width to any value (e.g. to 15cm). Now I change to "Page Layout" view and guess what... the width now shows up as something more than 16cm??! (The width property as points in VBA changes accordingly.) Switching to "Page Break View" will result in one more different width value. Is this "just" a big bug in Excel 2007 or did I miss something?? I know that the selected default printer or the chosen zoom factor may have an influence on the shown size of a shape, but these discrepancies are ...

appointment prints different time
Hi, My client uses a french CRM with date format dd/mm/yyyy. when he creates an appointment with the SFO client from e.g. 11 o'clock until 16 o'clock it is saved like correctly. but when he prints it out, the starttime is 8 o'clock and end time 13 o'clock. it is like that for every appointment. it always prints out 3 hours earlier. Anybody had this too? Thanx Cypress, You can check if both the server and the user are in the same time zone. Server: Open Control Panel, Date and Time, Timezone Client: On the laptop open Control Panel, Date and Time, Timezone Open CRM vi...

Different Columns on different pages
Hi - think this will be easily resolved but I am stuck! In publisher 2000 how do you create a multipaged, two page spread that has different columns on some pages. I keep bringing myself back to "ignore background" and trying to use "arrange" - "layout Guides" - but this isnt right. Am I thinking too deep? Is the whole point that the layout guides are simply guides and stay the same thorugh the whole document regardless.... you just use ruler guides to make the changes through the different pages? Any idea's? Thanks Julia ...

Difference 05-21-04
What is the difference between CRM standard and professional? -Johnny Johnny wrote: > What is the difference between CRM standard and professional? > > -Johnny Does it have anything to do with Outlook integration? -- - I am Johnny! Your Internet penpal. I have a scooter, how about you? "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:evfBJNwPEHA.3708@TK2MSFTNGP10.phx.gbl... > Johnny wrote: > > > What is the difference between CRM standard and professional? > > > > -Johnny > Does it have anything to do with Outlook inte...