Results 1 to 10 of 10
Thread: What Does This Do (Office 97)

20040725, 07:48 #1
 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
BraddyIf you are a fool at forty, you will always be a fool

20040725, 08:54 #2
 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

20040725, 09:27 #3
 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?
BraddyIf you are a fool at forty, you will always be a fool

20040725, 10:16 #4
 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

20040725, 10:21 #5
 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
BraddyIf you are a fool at forty, you will always be a fool

20040725, 10:26 #6
 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.

20040725, 18:17 #7
 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

20040726, 05:31 #8
 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?
BraddyIf you are a fool at forty, you will always be a fool

20040726, 06:51 #9
 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

20040726, 09:07 #10
 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.
BraddyIf you are a fool at forty, you will always be a fool