Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    What Does This Do (Office 97)

    Hi

    Can someone explain what this formula is doing please =COUNT(F7:F258,G7)

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What Does This Do (Office 97)

    It counts the total number of cells that in the range F7:F258 that contain numbers and adds it to the number of of cells that contain a number in the range G57 that has a number.

    It will vary from 0 to 253. (f7:F258 may contain up to 252 numbers and G57 may have 1. Blank cells, Text, and error values are not counted. it does not matter if the number is entered directly or via a formula. A date cell is also considered a number.

    What are you trying to do?
    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: What Does This Do (Office 97)

    Hi Steve

    This is a spreadsheet that I have Inherited, What happened was a copy was sent to each rep who filled in the applicable cells and returned it, then the data was copied into the master workbook, what I am trying to do is link the cells in the reps sheet to the master so that the data comes in automaticly, but if there is nothing in the reps cell, the link creates a 0, as consequence the count formula includes it in the count, I got around this by using data validation the user must enter a number or an x .

    Getting back to the origional question the I couldn't understand why the G57 was included in the formula. G57 sums the column, so if understand you,

    the result gives me the sum of F column plus the sum of Column G, is that correct?

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: What Does This Do (Office 97)

    I don't understand its context either since I only have the formula.

    <hr>the result gives me the sum of F column plus the sum of Column G, is that correct?<hr>

    No. It gives no sum whatsoever and it is not using columns: The items in col F are less than the full column and the item in col G is only 1 cell

    It is the total count of the numbers (including dates and formulas giving numbers) that are in the cells listed in the formula. Any blanks, text and errors are not counted.

    If you are after the sum of cols F and G, this would be:
    <pre>=SUM(F:G)</pre>


    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: What Does This Do (Office 97)

    Hi Steve

    Thanks for the explanation

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: What Does This Do (Office 97)

    The COUNT function does not calculate a sum (the SUM function does that), but it counts the number of cells that contain a number in the range or ranges provided.

    The formula looks at each of the cells in the range F7:F258 and then at the cell G7. Each cell that contains a number (including dates, currency amounts, percentages) contributes 1 to the result, each cell that is blank, contains a text value or contains an error such as #VALUE or #N/A contributes 0 to the result.

    It is impossible for us to say why the formula includes G57, since we don't know what the spreadsheet does.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What Does This Do (Office 97)

    Instead of forcing the reps to enter an x when there is not data, you could use a link like this:

    <pre>=IF([C:RepDirRepBook.xls]Sheet1!$A$1="","",[C:RepDirRepBook.xls]Sheet1!$A$1)
    </pre>


    That will give you a null string in the cell if the linked cell is null and COUNT will not count it.
    Legare Coleman

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: What Does This Do (Office 97)

    HI Legare

    Thanks for you reply
    Does your formula go directly into the cell or does it go into the data validation source?

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What Does This Do (Office 97)

    try entering in cell F7 of your Master sheet the following modification (relative ref)

    =IF([C:RepDirRepBook.xls]Sheet1!$F7="","",[C:RepDirRepBook.xls]Sheet1!$F7)

    Then copy this down thru F253 ??
    Then do your COUNT

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: What Does This Do (Office 97)

    Hi Paul

    Thanks very much.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •