Counting with spaces

Hi,

How do I count a total of things with spaces inbetween?

EX:

4/3/01   6

4/4/01   7

5/5/01  8
5/5/01 9
5/5/01 7

5/6/01  2

The answer I'm looking for is 4.  Each date would represent a total of
one.  The three entries on 5/5 would be counted as one.

Thanks,

Tom

0
invalid460 (432)
12/6/2006 7:37:48 PM
excel 39879 articles. 2 followers. Follow

2 Replies
454 Views

Similar Articles

[PageSpeed] 42

Sub numberOfGroups()
    Dim r As Range, x As Integer
    Set r = Range("A1")
    x = 0
    If r.Value = "" Then Set r = r.End(xlDown)
    Do
        If r.Offset(1, 0).Value = "" Then x = x + 1
        Set r = r.End(xlDown)
    Loop While r.Row < 65536
    If Not r.Value = "" Then x = x + 1
    MsgBox "Number of groups = " & x, , "Leppy's Code"
End Sub

This little sub should get you on your way to however you want to use
this.

Cheers,
Jason Lepack

Tom G wrote:
> Hi,
>
> How do I count a total of things with spaces inbetween?
>
> EX:
>
> 4/3/01   6
>
> 4/4/01   7
>
> 5/5/01  8
> 5/5/01 9
> 5/5/01 7
>
> 5/6/01  2
>
> The answer I'm looking for is 4.  Each date would represent a total of
> one.  The three entries on 5/5 would be counted as one.
> 
> Thanks,
> 
> Tom

0
jlepack (32)
12/6/2006 8:01:05 PM
On Wed, 6 Dec 2006 13:29:00 -0800, Ron Coderre
<ronREMOVETHIScoderre@bigfoot.com> wrote:

>Try this:
>
>This formula counts the unique items in A1:A10
>=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
>
>Adjust range references to suit your situation.
>
>Does that help?

Yes it works just fine, thank you.

Tom


>***********
>Regards,
>Ron
>
>XL2002, WinXP
>
>
>"Tom G" wrote:
>
>> Hi,
>> 
>> How do I count a total of things with spaces inbetween?
>> 
>> EX:
>> 
>> 4/3/01   6
>> 
>> 4/4/01   7
>> 
>> 5/5/01  8
>> 5/5/01 9
>> 5/5/01 7
>> 
>> 5/6/01  2
>> 
>> The answer I'm looking for is 4.  Each date would represent a total of
>> one.  The three entries on 5/5 would be counted as one.
>> 
>> Thanks,
>> 
>> Tom
>> 
>> 

0
invalid460 (432)
12/7/2006 4:24:06 AM
Reply:

Similar Artilces:

Counting occurances of pairs of values
I have 2 columns in my spreadsheet, I would like to be able to count the number of times pairs of values occur, e.g. col a | col b ------+------ foo | yes bar | yes foo | no foo | yes bar | yes bar | yes I'd like to search for all 'foo' AND 'yes' and then all 'bar' AND 'yes', so that I can generate a table: search | count -------+------ foo | 2 bar | 3 I've tried various combos of COUNTIF, SUM/IF but with no success. Anyone any ideas? Cheers -- Martin Hi Try using: =SUMPRODUCT((A2:A50="foo")*(B2:B50="yes")) a...

space bar deletes letters instead of creating a space
when I type a letter or message in e-mail, if I use the space bar to move a word forward, it deletes the first letter of word, when I type in new letter it deletes next leter and so on. Sounds like youre INSERT key is locked. "Big-thunder" <Big-thunder@discussions.microsoft.com> wrote in message news:F6D4784D-8E75-4395-A752-136DE5FF96F4@microsoft.com... > when I type a letter or message in e-mail, if I use the space bar to move > a > word forward, it deletes the first letter of word, when I type in new > letter > it deletes next leter and so o...

Highlight exact match in column and count
I am using this CF to find my duplicates =COUNTIF(A1:A$10000,A1)>1. The next part I need is to count the number of duplicates (gray shaded & strikethough). I have tried a few VBA but I read they don't work with formula based CF's. I could also sort the gray shaded then count them with a helper column but ideally would be have one cell at the bottom of my 8000 rows that total my duplicates. 'to display the distinct items in the range =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&"")) 'duplicate items in the range.. =COUNT...

syntax for count text field
Hi, I have a report bound in qryVendor in this query I have a fieldname: Stat with expression type Stat:IIF([VDB]=[TlVDB])="Close","") I want to count number of close in my report I name it Total No of CLOSED PO: can anyone is kind to help how to figure out thanks in advance Revned wrote: >I have a report bound in qryVendor >in this query I have a fieldname: Stat >with expression type > >Stat:IIF([VDB]=[TlVDB])="Close","") > >I want to count number of close in my report >I name it Total No of CLOSED P...

Day count conventions with YEARFRAC()
Hello, Would the results of YEARFRAC() only deviate between Actual/Actual and Actual/365 for a leap year? Thanks, Schiz ...

VBA to count colored cells
I have never used a VBA, but I am a really quick learner and a self taught excel user. What I have is a conditional format that highlights the row in blue if the G cell contains the word *lab*. Now what I need to do is have these cells not be counted in my graphs. I am using a countif formula - such as COUNTIF">1200" for time ranges. I want the rows with teh word lab in column G to NOT count in this. I was thinking a VBA could do this, but quite honestly I do not know how to do this. Any help would really be appriciated -thanks! hi see this site for countin...

Question about unallocated space
Hi All, Thanks in advance. I am running Windows Server 2003, and my C drive is running low on space. I am looking under Windows Disk Management and I see that I have 45GB of unallocated space. I want to be able to extend the space of the C drive to use the unallocated space. I tried using Diskpart,, it did not work, ,,that is why I am here,. Any ideas? I was looking at www.partitionwizard.com and GParted but I am unsure. Thanks again.. -- robsp ------------------------------------------------------------------------ robsp's Profile: http://forums.techarena.in/...

Persistent paragraph spacing settings
Hi all, Everytime I reply to an email with a signature, it adds an extra line between paragraphs. This looks terrible for multi-line entities such as mailing addresses. I use Outlook 2003 with Word 2003 as my email editor and the instructions from the following link do fix my problem. http://office.microsoft.com/en-us/assistance/HA011391891033.aspx Problem is, I have to perform this process for EVERY email I reply to (if it has a signature that triggers this nefarious effect). It keeps setting my "before" and "after" paragraph settings back to Auto (when they nee...

I'd like to know how we can count coloured cells?
I'd like to know how we can count coloured cells? i.e If cells A1 C1 E1 F1 and G1 are coloured, I’d like to know how I can see the total coloured cells in H1? I suppose it’s a little bit more complicated than it looks. Any help would be appreciated. Thank you. If they are coloured by conditional formatting, use those conditions to count the cells. If the cells are formatted separately, you can't do it with a formula; it needs VBA. -- David Biddulph "Atomic" <Atomic@discussions.microsoft.com> wrote in message news:950138A9-FBB9-4EEB-9D30-A7A...

Count of records IF...
I have a form that contains records about each file. In the form header, I have an unbound field that counts the total number of records. It works fine. What I want is ANOTHER field in the form header that also gives me a count of all the records, but only if a certain checkbox on the form is checked (and by extension, that particular field in the record is -1). I'm sure this is panfully easy to do, but its escaping me right now. Create a text box in the Form Header and in it control source write =Sum(IIf([YesNoFieldName]=True,1,0)) -- Good Luck BS"D "Nathan" wr...

count rownumbers in loop
column -------------- column ------------ column A (Name) B(code #) C(total persons working under him) 1 JOHN 4 2 CARY 6 3 LARA 1 = ? 4 DAVID 0 = ? 5 LUCY 6 6 RAO 4 Range B1:B6 contains code numbers(in text format) represent range A1:A6 (persons) immediate Boss's row number.JOHN's row number is 1 and his code no is 4(B1),that i...

Receipt count based on tender type
My store uses all of the typical tender types as well as house accounts for select customers. When I tender an invoice on account, I need to get 2 receipts (one the customer signs for us, and one for the customer) but for all other tenders I only need 1 copy. I found that relying on my employees to remember to print a duplicate manually is not sufficient so I have created an elaborate work around by modifying the XML code for the receipt template. My work around works, but now if I reprint any on account invoice from the journal, it always prints duplicate copies. Is there any way to pr...

TRIM spaces
I have first name and last name in one cell. Between the first name and last name, there is a space. I want to put the first name in one cell and the last name in another cell. Is TRIM the right function to use? If not, how would I accomplish this? Thanks in advance. Select the column with the names, then <Data> <TextToColumns> Make sure "Delimited" is checked, then <Next>, Put a check in "Space", then <Finish>. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within ...

HTML Email Problem: Odd vertical space
Hi, I've tried many different HTML solutions but cannot resolve this problem. Perhaps someone can help me and the person who asked for my help? I have a two column HTML email that displays fine in every email software I've tested except for Outlook 2007. In Outlook 2007, for some reason there is a vertical gap between two ads (images) stacked in the right side column of the email. So far, the only way I've eliminated the gap is to eliminate the left column text. Obviously that is not an option. Has anyone seen this sort of error in how Outlook 2007 renders HTML email? Here'...

character count
Hi, Is there a function which will count the character in a cell? Thanks. =LEN(A1) -- Kind Regards, Niek Otten Microsoft MVP - Excel "supernova" <abc@yahoo.com> wrote in message news:%23YmTDne1DHA.2872@TK2MSFTNGP09.phx.gbl... > Hi, > > Is there a function which will count the character in a cell? > Thanks. > > Hi ? =Len(A1) -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "supernova" <abc@yahoo.com> wrote in message news:%23YmTDne1DHA.2872@TK2MSFTNGP09.phx.gbl... > Hi, > &g...

Stock Count Forms
Customer is on GP9 using Stock Counts. They are trying to print their stock count forms - and they have provided a screen shot of the STOCK COUNT MASS ADD window - it includes an option to Include Site/Items with zero on hand quanity- they are leaving this unchecked - expecting those items that have an on hand of zero to not show up the reports - they are showing up. In my GP9 - i don't have this option - Please help... ...

remove space and chars. replace() does not work
I need to remove characters and spaces from a database field and replace() is not working for me. I dont know if I am using it properly. I am putting it in the queries window and it says I cannot use it without select or update. Can some1 help me on how exactly to use replace(). Thank you in advance. removeChars wrote: >I need to remove characters and spaces from a database field and >replace() is not working for me. I dont know if I am using it >properly. I am putting it in the queries window and it says I cannot >use it without select or update. Can some1 help me on how exact...

Count Occurances
Using Excel 2003. I will try to explain this the best way I can. I have a spreadsheet that compiles trouble call data. Every call that is logged has a ID# for that location. There are about 900 locations. In the course of a year not all locations will have a trouble call. I want to see who had how many calls. I know I can write countif statements for each of the 900 locations, but I really do not want to do that. I want know exactly who had troubles. If I have the data as to who had multiple calls, I can plug each one into my pivot table to show the nature of the calls. ...

Counting Text Cells
Hi, all. I have a column with many different names. I can count all the cells with the name, Bob for example: countif(a8:a1000,"Bob"), but what if I want to get a running total of all the cells with names in them, but not the blank cells. How do I do that? Thanks, Frank West =count(a8:a1000) will give count of all non-blank cells with text values =countblank(a8:a1000) will give count of all blank cells =count(a8:a100) will count all the numbers in the range. --- Message posted from http://www.ExcelForum.com/ On Wed, 31 Dec 2003 01:19:38 -0600, Kieran <Kieran.zaira@exce...

Counting Between Dates
Hello All, I know this question seems redundant, but I can't seem to find a pos that resembles the question I have. I am trying to count the number o days between to dates through a 10 year, daily period. I have a colum of excursion periods that range from 10 days to 15 days to 75 days etc. Every period has a different number of days. Instead o scrolling down to each period and simply counting the cells betwee dates, I would like to write a function that will do all the summin for me. Is this a countif nested in an IF block? I'll try to say this again in a different way: I need...

Space Bar Reading
I have a question about the space bar reading feature. I really, really want to use this feature because it looks pretty handy. However, I have my email sorted so that my newest messages appear at the top. When I try to use space bar reading, it doesn't go to the next newest message, but the next oldest message. So the space bar reading ends up working in reverse, making it no good to me. Is there any option somewhere to change this? I've looked and haven't found anything. ...

double space signature
I have been using a signature on my outlook 2003 emails and it has been fine excepr on replys it shows up doublespaced. also I just added a graphic image to my signature in order to do this I had to go in to advanced edit. now on all emails my signature shows up doublespaced and on replys the graphic image does not show up at all. another issue along the same line I have 2 different email accounts set up one with a signature and one with out. If I set the email account that does not have a signature as the default account my signature dose not display when I choose the other email ac...

~Count Between~
Hi, I have a xl file that contains Intime wise sheets of route charts that contains the name & address details of our resources. Each intime(28 in total) will have approx 10-30 routes with 2 rows of heading for each. I need to extract no of resources for each route with the help of an formula.. Is there any way to do this.?! Thanks in advance. Vinod... Hi, B coulam of every header(2nd row) will be "NAME" . The content of the 2 heading rows will have small changes but the above mentioned will be the same in all the routes Vinod.... If desired...

count
I have a report that I have been emailing help on with you guys. Everyone has been such a great help. My report is a 12 month rolling report, which is based off of a crosstab query. I need a field called count. This will count what months have data in them. So for example: Item Mth1 Mth2 Mth3 Mth4 Mth5 Mth6 Mth7 Mth8 Mth9 Mth10 Mth11 Cnt 12 2 0 0 0 0 3 3 6 2 0 6 The count being which months are > 0. Do I create this in my query or better off in my report? Thanks in advance KimP -- Message posted ...

Exporting to "prn" adding extra spaces with Excel 2003
When I export my work sheet to a "prn" format, it adds extar spaces to the data. I did not notice this when I veiw the data in "Notepad" but when I view the file using the "Edit" function in a DOS window, I can see the spaces. I have been exporting to "prn" for 4 years now using Excel 2000 and Excel 2002 with no problems. Does anyone know of a switch or toggle to prevent Excel 2003 from adding the extra spaces when exporting to "prn". That's the purpose of a PRN file. In a file of that type, columns are fixed width, and data from ...