a count field in a query?

Hello,

I have a query in which I would like to create a field which
increments by 1 for each record selected, so if there are 10 records
selected by the query, this column would show numbers 1 to 10.

Ideally I would like to have some text in front of each number, say
invoice1, invoice2 etc.

From a previous posting I realise that this is frowned upon but it
would be the simplest solution for my problem!

How can I do this?

Thanks

Geoff
0
Geoff
12/3/2007 8:54:00 AM
access 16762 articles. 2 followers. Follow

11 Replies
1213 Views

Similar Articles

[PageSpeed] 29

On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote:

>Hello,
>
>I have a query in which I would like to create a field which
>increments by 1 for each record selected, so if there are 10 records
>selected by the query, this column would show numbers 1 to 10.
>
>Ideally I would like to have some text in front of each number, say
>invoice1, invoice2 etc.
>
>From a previous posting I realise that this is frowned upon but it
>would be the simplest solution for my problem!

If you want this for a Report, it's easy: put a textbox on the report with a
control source

=1

and set its Running Sum property to Over Group (or Over All as appropriate).

It's also possible in a query, although less efficient. You do noeed some
field in the query which is strictly in ascending order - an autonumber ID is
one possibility, a datestamp is another. I'll call it Sortfield, you would use
your own fieldname. In a vacant field cell type

=DCount("*", "[yourqueryname]", "[sortfield] <=" & [sortfield])


             John W. Vinson [MVP]
0
John
12/3/2007 7:36:10 PM
On Mon, 03 Dec 2007 12:36:10 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Mon, 03 Dec 2007 08:54:00 +0000, Geoff Cox <<>> wrote:
>
>>Hello,
>>
>>I have a query in which I would like to create a field which
>>increments by 1 for each record selected, so if there are 10 records
>>selected by the query, this column would show numbers 1 to 10.
>>
>>Ideally I would like to have some text in front of each number, say
>>invoice1, invoice2 etc.
>>
>>From a previous posting I realise that this is frowned upon but it
>>would be the simplest solution for my problem!
>
>If you want this for a Report, it's easy: put a textbox on the report with a
>control source
>
>=1
>
>and set its Running Sum property to Over Group (or Over All as appropriate).
>
>It's also possible in a query, although less efficient. You do noeed some
>field in the query which is strictly in ascending order - an autonumber ID is
>one possibility, a datestamp is another. I'll call it Sortfield, you would use
>your own fieldname. In a vacant field cell type
>
>
>
>
>             John W. Vinson [MVP]

Thanks John,

I have used 

=DCount("*", "[renewal-end-dec-07]", "[ID] <=" & [ID])

and this does give 1 to 13 but a couple of things.

Could you explain how it works and is it possible to introduce some
text so that I get for example,

renewal_number_1 etc?

Cheers

Geoff


0
Geoff
12/3/2007 9:52:01 PM
On Mon, 03 Dec 2007 21:52:01 +0000, Geoff Cox <<>> wrote:

>I have used 
>
>=DCount("*", "[renewal-end-dec-07]", "[ID] <=" & [ID])
>
>and this does give 1 to 13 but a couple of things.
>
>Could you explain how it works

The DCount function (like all the Domain functions, DLookUp, DSum, etc.) takes
three arguments. The first is the field to be retrieved. In the special case
of DCount, you can use * to say "count all records, I'm not interested in any
particular field". The second argument is the name of a table or query from
which you wish to retrieve - in this case renewal-end-dec-07. 

The third argument is a search criterion which specifies which records to
retrieve. In this case, you have a field named ID in renewal-end-dec-07 that
(I assume) has a different value for each record, and by which you're sorting
the records. The somewhat confusing criterion

"[ID] <=" & [ID]

looks at the ID value in *this current* record, and tells DCount to count up
all the records with an ID less than or equal to that value. For example, if
the current record in the query has ID value 1, it will find just that one
record; if you have ID values 1254 through 1263, and you're looking at the
record with 1263, it will count ten records.

>and is it possible to introduce some
>text so that I get for example, renewal_number_1 etc?

DON'T confuse data PRESENTATION with data CALCULATION.

renewal_number_1 *is not a number*. It *is not a count*. It cannot be the
result of a mathematical calculation!

Just display the text, just like we were discussing in the other thread. The
way (well, one way) to do so is to use the Format property of the control in
which you display this count. If you set the Format property to

"renewal_number_#"

Access will treat the text renewal_number_ as just "wallpaper", displaying
that text string on every record; the # is a valid Format character meaning to
display the value as a number.


             John W. Vinson [MVP]
0
John
12/4/2007 1:04:14 AM
On Mon, 03 Dec 2007 18:04:14 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:


>the records. The somewhat confusing criterion
>
>"[ID] <=" & [ID]
>
>looks at the ID value in *this current* record, and tells DCount to count up
>all the records with an ID less than or equal to that value. For example, if
>the current record in the query has ID value 1, it will find just that one
>record; if you have ID values 1254 through 1263, and you're looking at the
>record with 1263, it will count ten records.

John,

OK - I understand that now - thanks.
>
>>and is it possible to introduce some
>>text so that I get for example, renewal_number_1 etc?
>
>DON'T confuse data PRESENTATION with data CALCULATION.
>
>renewal_number_1 *is not a number*. It *is not a count*. It cannot be the
>result of a mathematical calculation!
>
>Just display the text, just like we were discussing in the other thread. The
>way (well, one way) to do so is to use the Format property of the control in
>which you display this count. If you set the Format property to
>
>"renewal_number_#"

this I don't get! at the moment I create the invoice_number field in
the query using 

invoice_number: DCount("*","[renewal-end-dec-07]","[ID] <=" & [ID])

and then I am using some mailing software which looks at this query
and sends out an email for each record in the query. At the moment the
emails have the numbers 1 to 13, there being 13 records found by the
query.

 How do I get them to appear as  renewal_number_1 etc? I don't
understand where I use the "renewal_number_#"!

Cheers

Geoff


>
>Access will treat the text renewal_number_ as just "wallpaper", displaying
>that text string on every record; the # is a valid Format character meaning to
>display the value as a number.
>
>
>             John W. Vinson [MVP]
0
Geoff
12/4/2007 3:36:09 AM
On Tue, 04 Dec 2007 03:36:09 +0000, Geoff Cox <<>> wrote:

>and then I am using some mailing software which looks at this query
>and sends out an email for each record in the query. At the moment the
>emails have the numbers 1 to 13, there being 13 records found by the
>query.

Thanks. You didn't say that, and my telepathy wasn't working correctly. I
thought you wanted to display the value in a Form or Report, rather than
having it in a query datasheet, since that's how data is typically displayed.

> How do I get them to appear as  renewal_number_1 etc? I don't
>understand where I use the "renewal_number_#"!

Change my suggestion, then. In the calculated field type

ExpSeq: "renewal_number_" & DCount("*", <etcetera>)

The & operator concatenates the literal text string "renewal_number_" with the
result of the dcount expression.

             John W. Vinson [MVP]
0
John
12/4/2007 4:16:28 AM
On Mon, 03 Dec 2007 21:16:28 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Tue, 04 Dec 2007 03:36:09 +0000, Geoff Cox <<>> wrote:
>
>>and then I am using some mailing software which looks at this query
>>and sends out an email for each record in the query. At the moment the
>>emails have the numbers 1 to 13, there being 13 records found by the
>>query.
>
>Thanks. You didn't say that, and my telepathy wasn't working correctly. I
>thought you wanted to display the value in a Form or Report, rather than
>having it in a query datasheet, since that's how data is typically displayed.
>
>> How do I get them to appear as  renewal_number_1 etc? I don't
>>understand where I use the "renewal_number_#"!
>
>Change my suggestion, then. In the calculated field type
>
>ExpSeq: "renewal_number_" & DCount("*", <etcetera>)
>
>The & operator concatenates the literal text string "renewal_number_" with the
>result of the dcount expression.
>
>             John W. Vinson [MVP]

John,

Got it!!

I had gone to bed then thought I'd better tell you that I had tried
various format options wihout success!

The moral being always state the question clearly!

Many thanks.

Cheers

Geoff




0
Geoff
12/4/2007 4:33:38 AM
On Mon, 03 Dec 2007 21:16:28 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:


>Change my suggestion, then. In the calculated field type
>
>ExpSeq: "renewal_number_" & DCount("*", <etcetera>)
>
>The & operator concatenates the literal text string "renewal_number_" with the
>result of the dcount expression.
>
>             John W. Vinson [MVP]

John,

Is it possible to get the date involved in this?

ExpSeq: "renewal_number_"&  date  & DCount("*", <etcetera>)

doesn't work.

Ideally I would like to create

renewal_number_dec_07_1, renewal_number_jan_08_1, etc,  then the query
would work OK for each month it is used.

Is this a step too far or does it require VBA code?

Cheers

Geoff


0
Geoff
12/4/2007 4:58:30 AM
On Tue, 04 Dec 2007 04:58:30 +0000, Geoff Cox <<>> wrote:

>ExpSeq: "renewal_number_"&  date  & DCount("*", <etcetera>)
>
>doesn't work.
>
>Ideally I would like to create
>
>renewal_number_dec_07_1, renewal_number_jan_08_1, etc,  then the query
>would work OK for each month it is used.
>
>Is this a step too far or does it require VBA code?
>
>Cheers

"renewal_number_" & Format(Date(), "mmm-yy") & "_" & DCount(...

will put the current date's month and year in the string. Replace Date() with
the name of a date/time field in your table to get that date. Is 08 the year
or the day? You can use yy for the year, dd for the day in the Format()
function call.

             John W. Vinson [MVP]
0
John
12/4/2007 7:03:47 AM
On Tue, 04 Dec 2007 00:03:47 -0700, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

>On Tue, 04 Dec 2007 04:58:30 +0000, Geoff Cox <<>> wrote:
>
>>ExpSeq: "renewal_number_"&  date  & DCount("*", <etcetera>)
>>
>>doesn't work.
>>
>>Ideally I would like to create
>>
>>renewal_number_dec_07_1, renewal_number_jan_08_1, etc,  then the query
>>would work OK for each month it is used.
>>
>>Is this a step too far or does it require VBA code?
>>
>>Cheers
>
>"renewal_number_" & Format(Date(), "mmm-yy") & "_" & DCount(...
>
>will put the current date's month and year in the string. Replace Date() with
>the name of a date/time field in your table to get that date. Is 08 the year
>or the day? You can use yy for the year, dd for the day in the Format()
>function call.
>
>             John W. Vinson [MVP]

John,

Fine! Thanks yet again. Yes 08 was the year.

I just wonder where I might find this level of information re the use
of, for example,  DCount, apart from taking up your time?!

I have just bought the Access 2003 Bible, all 1362 pages, but even it
doesn't go beyond a brief bit about DCount.

Any suggestions?

Cheers

Geoff
0
Geoff
12/4/2007 9:00:35 AM
On Tue, 04 Dec 2007 09:00:35 +0000, Geoff Cox <<>> wrote:

>I just wonder where I might find this level of information re the use
>of, for example,  DCount, apart from taking up your time?!
>
>I have just bought the Access 2003 Bible, all 1362 pages, but even it
>doesn't go beyond a brief bit about DCount.
>
>Any suggestions?

Open the VBA editor (for example by pressing Ctrl-G).
Press the F1 key or select Help.
Search for the name of the function you want to learn about.
If need be, press the "related issues" link.

             John W. Vinson [MVP]
0
John
12/4/2007 5:57:23 PM
Geoff Cox < wrote:

> I just wonder where I might find this level of information re the use
> of, for example,  DCount, apart from taking up your time?!
> 
> I have just bought the Access 2003 Bible, all 1362 pages, but even it
> doesn't go beyond a brief bit about DCount.
> 
> Any suggestions?
> 
> Cheers
> 
> Geoff

Are there any discernable differences between:

DLookup("Count(*)", "tblShippers", "SID >= 1000")
DCount("*", "tblShippers", "SID >= 1000")

DLookup("Sum([InvoiceAmount])", "tblShippers")
DSum("[InvoiceAmount]", "tblShippers")

DLookup("Max([InvoiceAmount])", "tblShippers")
DMax("[InvoiceAmount]", "tblShippers")

DLookup("Min([InvoiceAmount])", "tblShippers")
DMin("[InvoiceAmount]", "tblShippers")

DLookup("Avg([InvoiceAmount])", "tblShippers")
DAvg("[InvoiceAmount]", "tblShippers")

DLookup("First([InvoiceAmount])", "tblShippers")
DFirst("[InvoiceAmount]", "tblShippers")

DLookup("Last([InvoiceAmount])", "tblShippers")
DLast("[InvoiceAmount]", "tblShippers")

DLookup("StDev([InvoiceAmount])", "tblShippers")
DStDev("[InvoiceAmount]", "tblShippers")

DLookup("StDevP([InvoiceAmount])", "tblShippers")
DStDevP("[InvoiceAmount]", "tblShippers")

DLookup("Var([InvoiceAmount])", "tblShippers")
DVar("[InvoiceAmount]", "tblShippers")

DLookup("VarP([InvoiceAmount])", "tblShippers")
DVarP("[InvoiceAmount]", "tblShippers")

?

If not, then the information about DLookup is enough.  Unless, of 
course, the book doesn't cover DLookup :-).

James A. Fortune
MPAPoster@FortuneJames.com
0
James
12/4/2007 9:47:57 PM
Reply:

Similar Artilces:

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

How to validate sum of fields (access 2003)
Hi, I'm running a query in order to find records that have at least one match, based on a form - where I select a certain record - and a subform - where the matching records are displayed. The search is based on four parameters (located in the main form), which are scored from 0 to 1 (passing through 0,25, 0,5 and 0,75). The results that appear in the subform are ranked and filtered according to the sum of those four criteria and corresponding scores. Now, my problem is very simple but so far I haven't found a solution that works: I want to validate the sum of those f...

Integration Manager error: unknown Great Plains field subtype '243
When a user with class 'ARSupvisor' tried to upload invoices to GP, an error message show up as in the title. We have to set to 'Accounting Mgr' to have the upload done successfully. Can someone tell why it is like this? What can we change to make a 'ARSupvisor' be able to upload? (access right, etc) Thanks in advance! Kevin I have not tried this in version 10 yet, but I know in previous versions of GP, a user had to have access to the SOP Setup screen to be able to import SOP invoices using Integration Manager. Hope that helps. -- Victoria Yudin Dynamics GP...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

1. Selecting field with "enter"; 2. permanently accepting macros
I have recently upgraded to Access 2007 and two characteristics of my former database are not coming through & I want to get them back. First, in Access 2003, I had set it up so that if I hit "enter" the entire field would be selected. Now "enter" causes the cursor to jump to the next field. How can I change this? Secondly, In my database forms, I had incorporated buttons. Now, each time I open the database, I am given a warning about macros and I have to manually tell the program that the macros are trusted. Unfortunately, Access doesn't accept this an...

Store Operations POS Item Table Field Suggestion
We would like to suggest adding a new "numeric" type field in the "Item" table of Store Operations. All of the customers that we have dealt with have only used numeric values in the "ItemLookupCode" field, however this field is an alphanumeric string, and does not offer true numeric sorting in applicable browsers. Not to alienate those clients that use alphanumeric characters in the ItemLookupCode field, we suggest adding several true numeric fields for custom data, then the user could sort on their numeric data. In addition to our encounters with numeri...

Alternating fields
I wish to produce a list of alternately male and females records. With a gender field, how could this be achieved? With Gender in column B, create a new column for Count. With the first gender in B2, enter the following in row 2 of the Count column: =COUNTIF($B$1:B1,B2)+1 Copy the formula down to the last row of data Select a cell in the table Choose Data>Sort Sort by Count, then by Gender Shub wrote: > I wish to produce a list of alternately male and females records. > > With a gender field, how could this be achieved? > > -- Debra Dalgleish Excel FAQ, ...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Query in a sub form
Hello, I have a form with a subform in Access 2003. One of the fields in the subform is called categoryid. I am trying to base a combobox on a query which looks up the value of the categoryid of the current record on the subform and then displays an appropriate list for that categoryid in the combobox. In the query I use the following line in the criteria [Forms]! [TblOrderDetails1]![TblItemOrderDetails Subform].[Form]![CategoryID] However, this query only works for the first record in the subform. Regardless of which record the combobox is in, it always looks up the CategoryID...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

Populate Blank Fields with "0" based on checkmark.
I have a form with 20 fields. The user enters data into some but not all of the fields depending on where they collected data. Is it possible to create a checkbox where, after the user enters their data, they click the checkbox, and any of the 20 fields that are blank get populated with a 0? Thanks for the help. Is there any reason you want them to be zero? If they are numeric fields, you can set the default to zero in table design view. There is no need to use a checkbox, you can arrange it so any empty fields are converted to zero when you move to another record or exit th...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

Multiply all query by same value
...

Counting sales
I have two different datasets. Each with a common identifier. I need a formula that will reference the identifiers in both datasets and count the number of sales a particular agent made. Example. look up this identifier in the other dataset and then give me the value that is 2 columns over from the identifier in the second dataset. See if this helps: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Dave" <Dave@discussions.microsoft.com> wrote in message news:E7245B37-033F-400E-9CEE-9822BE7CB0F5@microsoft.com... >I have two...

Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRa...

COUNT ? need formula
Hi, I want to track the results of my teams sales performance. I record if it is a sale, no sale, cancelled etc in column M. I thought I'd be able to use the COUNT function/formula to be able to search for all the SALES in column M and place the result in a cell ( column O )but I can't work it out. Any ideas? Damian Hi! Try this: =COUNTIF(M1:M100,"sale") Biff "Diamond Jones" <kwanzaNOSPAM@optusnet.com.au> wrote in message news:43a0e897$0$17704$afc38c87@news.optusnet.com.au... > Hi, > > I want to track the results of my teams sales perf...

telephone number field mask
I have a field I use for telephone numbers. I would like the mask to return data like this : 111 111 1111. What is the formula for this? Any help is appreciated. Mtn244: Thanks much for assistance. I tried your mask with great success. All is good in River City. Thanks again for your help. Frank "mtn244" <mtn244@discussions.microsoft.com> wrote in message news:82895481-BC0D-4FB7-A9A8-1649395C1224@microsoft.com... > Try this input mask: > !999 000 0000;0; > > "Frank" wrote: > >> I have a field I use for telephone numbers. I would lik...

Count Function
I am creating a report for the number of pupils being collected at a certain collection time e.g. 14:00hr and 15:00hrs using the formula =Count([Child Last Name]) in the collection time footer However when i try to count the overall number of pupils in the footer using the same formula, 'Error' appears on screen? Thanks There are potentially a dozen footer sections in a report. Which one is giving you a problem? What is the name of the control? What is the exact formula? -- Duane Hookom Microsoft Access MVP "ELo" wrote: > I am creating a r...

Counting a count
How do I count a count?? This is what im trying to do: I have a database with a table named tblMain and has incorporated everything within that table but contains info from about 8 other tables. I have not doesigned this database and it appears to have a very poor structure...anyway There are three fields involved in this process...'Request' (either 'dispensation', 'waiver' or 'dispensation and waiver'), 'Department' (which consists of 7 other drop down menu options) and 'RequestStatus' which contains the status of each request by another...

Number format / field size
I want to set a Primary key with 16 digits - 4 groups of 4 numbers with spaces between - which I can use in a calculated field to increment. I have tried every permutation I can think of, can anyone help please? Use 4 separate fields, put contraints so that none of them can exceed 9999, and concatenate them into a single field in a calculated field in a query. Use the query wherever you would otherwise have used the table. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Kitty R" <Kitty R@discussions.microsoft.com> wrote in mess...

Table or Query?
I've been racking by brain and various books to find some answers, but to no avail. I need assistance with a simple task. I have a combo box that looks up items from a table. Once an item is selected, I do not want it to appear again. Should I be using a query for my control source? Please help!! Any assistance would be greatly appreciated. Thank you!!! When you say "Once an item is selected, I do not want it to appear again.", what is the scope? Do you mean never ever again or do you mean while the form is open? It makes a difference in how you will approach this. ...

2002 Pivot Table Calculating Field
Can you place a calculated field in a Column next to an Excel List field? Example: Total Quater1 % Quarter2 % Quarter3 % Quarter4 % Sales Sales Sales Sales Sales 240 10 4 75 31 105 44 50 21 The % field is not in the Excel List. List example: Area Quarter Sales 1 2 50 2 3 75 3 2 25 4 1 10 5 4 35 6 ...

Count Problem
Hi I have 20 cells in a single column in a sheet called "Audits" All the cells have a formula in them. The formula is a simple one that reads whatever I put into a cell on a different worksheet called "Names" ie: =Names!A1 = Names!A2 etc etc I would like to have a formula that counts up all the results I get in the sheet called "Audits" At the moment if I enter (let say) 15 names in the "Names" sheet the result is that I see the same 15 names in the "Audits" sheet, however the COUNTA formula returns a total of 20. SO obviously...