Using "Sum" to add values from records in detail section

I have a report that contains a customer ID header, customer detail section, 
and customer ID footer. In the detail section i have 4 fields that i am 
applying an equation to, and performing that in a 5th field unbound control 
=([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point 
Value]). When i view the report it gives me the correct number for this 
calculation (the 5th field name where this calculation is happening is called 
"Text48"). In the footer section i am now trying to add up a total of every 
record that shows up in the detail section like so =Sum([Text48]). I'm trying 
to sum up all the detail records of Text48.
Its not working and the report does not recognize Text48. Can anybody help??
0
Utf
3/21/2008 4:06:01 AM
access.reports 4434 articles. 0 followers. Follow

8 Replies
640 Views

Similar Articles

[PageSpeed] 26

Hi Adam,

You can't sum contents of unbound/calculated text controls; you can only sum 
fields in the report's recordsource.

So, what you need in the group/report footer, to sum the items in the detail 
section's unbound textbox, is the same expression in a Sum function:
    =Sum(([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category 
Point Value]))

In the Customer footer section, this will give the total for each group; in 
the Report footer, it will give the total for all customer groups.

HTH,

Rob

"Adam" <Adam@discussions.microsoft.com> wrote in message 
news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
>I have a report that contains a customer ID header, customer detail 
>section,
> and customer ID footer. In the detail section i have 4 fields that i am
> applying an equation to, and performing that in a 5th field unbound 
> control
> =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> Value]). When i view the report it gives me the correct number for this
> calculation (the 5th field name where this calculation is happening is 
> called
> "Text48"). In the footer section i am now trying to add up a total of 
> every
> record that shows up in the detail section like so =Sum([Text48]). I'm 
> trying
> to sum up all the detail records of Text48.
> Its not working and the report does not recognize Text48. Can anybody 
> help?? 

0
Rob
3/21/2008 6:00:03 AM
Just as a matter of interest, Adam, is this report based on a  Crosstab
Query or do you have one column in your Table called Set 1 Reps, one called
Set 2 Reps etc?
Evi

"Adam" <Adam@discussions.microsoft.com> wrote in message
news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> I have a report that contains a customer ID header, customer detail
section,
> and customer ID footer. In the detail section i have 4 fields that i am
> applying an equation to, and performing that in a 5th field unbound
control
> =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> Value]). When i view the report it gives me the correct number for this
> calculation (the 5th field name where this calculation is happening is
called
> "Text48"). In the footer section i am now trying to add up a total of
every
> record that shows up in the detail section like so =Sum([Text48]). I'm
trying
> to sum up all the detail records of Text48.
> Its not working and the report does not recognize Text48. Can anybody
help??


0
Evi
3/21/2008 9:40:25 AM
its the later, the columns in a table (one for each set)...is there another 
way to do this?

"Evi" wrote:

> Just as a matter of interest, Adam, is this report based on a  Crosstab
> Query or do you have one column in your Table called Set 1 Reps, one called
> Set 2 Reps etc?
> Evi
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message
> news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > I have a report that contains a customer ID header, customer detail
> section,
> > and customer ID footer. In the detail section i have 4 fields that i am
> > applying an equation to, and performing that in a 5th field unbound
> control
> > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> > Value]). When i view the report it gives me the correct number for this
> > calculation (the 5th field name where this calculation is happening is
> called
> > "Text48"). In the footer section i am now trying to add up a total of
> every
> > record that shows up in the detail section like so =Sum([Text48]). I'm
> trying
> > to sum up all the detail records of Text48.
> > Its not working and the report does not recognize Text48. Can anybody
> help??
> 
> 
> 
0
Utf
3/22/2008 6:10:01 PM
I don't know what your db is about but it does sound horribly as if your
database is not designed correctly .

What happens if you want to add a Set 5 Reps. Do you have to add another
field column to your database, forms, reports, calculations etc?

Or have I completely misunderstood something.

Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
query on which your report is based. (if it is based on a table,change it
into a query by clicking next to Record Source, let the Query Builder open a
query in Design View and add all the fields to your query grid.

While in Design View, at the next empty column type, in the first row next
to the other field names, type in:

TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps]  *
[Category Point]
Check in the normal Datasheet view to see if its doing what you think it
should.

Use the Field List button and drag  the TotReps field from  there into your
report instead of the unbound text box.

In your report and customer footers, you can now use
Sum ([TotReps])

Evi


"Adam" <Adam@discussions.microsoft.com> wrote in message
news:773D267F-75C5-40F1-BD87-F50216715988@microsoft.com...
> its the later, the columns in a table (one for each set)...is there
another
> way to do this?
>
> "Evi" wrote:
>
> > Just as a matter of interest, Adam, is this report based on a  Crosstab
> > Query or do you have one column in your Table called Set 1 Reps, one
called
> > Set 2 Reps etc?
> > Evi
> >
> > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > > I have a report that contains a customer ID header, customer detail
> > section,
> > > and customer ID footer. In the detail section i have 4 fields that i
am
> > > applying an equation to, and performing that in a 5th field unbound
> > control
> > > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> > > Value]). When i view the report it gives me the correct number for
this
> > > calculation (the 5th field name where this calculation is happening is
> > called
> > > "Text48"). In the footer section i am now trying to add up a total of
> > every
> > > record that shows up in the detail section like so =Sum([Text48]). I'm
> > trying
> > > to sum up all the detail records of Text48.
> > > Its not working and the report does not recognize Text48. Can anybody
> > help??
> >
> >
> >


0
Evi
3/22/2008 7:29:18 PM
should these types of caclulations be done in the queriy in stead of the 
report so that i can always have it in the quiery before any report needs to 
run?

"Evi" wrote:

> I don't know what your db is about but it does sound horribly as if your
> database is not designed correctly .
> 
> What happens if you want to add a Set 5 Reps. Do you have to add another
> field column to your database, forms, reports, calculations etc?
> 
> Or have I completely misunderstood something.
> 
> Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
> query on which your report is based. (if it is based on a table,change it
> into a query by clicking next to Record Source, let the Query Builder open a
> query in Design View and add all the fields to your query grid.
> 
> While in Design View, at the next empty column type, in the first row next
> to the other field names, type in:
> 
> TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps]  *
> [Category Point]
> Check in the normal Datasheet view to see if its doing what you think it
> should.
> 
> Use the Field List button and drag  the TotReps field from  there into your
> report instead of the unbound text box.
> 
> In your report and customer footers, you can now use
> Sum ([TotReps])
> 
> Evi
> 
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message
> news:773D267F-75C5-40F1-BD87-F50216715988@microsoft.com...
> > its the later, the columns in a table (one for each set)...is there
> another
> > way to do this?
> >
> > "Evi" wrote:
> >
> > > Just as a matter of interest, Adam, is this report based on a  Crosstab
> > > Query or do you have one column in your Table called Set 1 Reps, one
> called
> > > Set 2 Reps etc?
> > > Evi
> > >
> > > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > > news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > > > I have a report that contains a customer ID header, customer detail
> > > section,
> > > > and customer ID footer. In the detail section i have 4 fields that i
> am
> > > > applying an equation to, and performing that in a 5th field unbound
> > > control
> > > > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> > > > Value]). When i view the report it gives me the correct number for
> this
> > > > calculation (the 5th field name where this calculation is happening is
> > > called
> > > > "Text48"). In the footer section i am now trying to add up a total of
> > > every
> > > > record that shows up in the detail section like so =Sum([Text48]). I'm
> > > trying
> > > > to sum up all the detail records of Text48.
> > > > Its not working and the report does not recognize Text48. Can anybody
> > > help??
> > >
> > >
> > >
> 
> 
> 
0
Utf
3/22/2008 8:18:00 PM
should it always calculate in the query instead of the rprt? so that this way 
the rrprt does not have to run in order for me to get the value?

"Evi" wrote:

> I don't know what your db is about but it does sound horribly as if your
> database is not designed correctly .
> 
> What happens if you want to add a Set 5 Reps. Do you have to add another
> field column to your database, forms, reports, calculations etc?
> 
> Or have I completely misunderstood something.
> 
> Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
> query on which your report is based. (if it is based on a table,change it
> into a query by clicking next to Record Source, let the Query Builder open a
> query in Design View and add all the fields to your query grid.
> 
> While in Design View, at the next empty column type, in the first row next
> to the other field names, type in:
> 
> TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps]  *
> [Category Point]
> Check in the normal Datasheet view to see if its doing what you think it
> should.
> 
> Use the Field List button and drag  the TotReps field from  there into your
> report instead of the unbound text box.
> 
> In your report and customer footers, you can now use
> Sum ([TotReps])
> 
> Evi
> 
> 
> "Adam" <Adam@discussions.microsoft.com> wrote in message
> news:773D267F-75C5-40F1-BD87-F50216715988@microsoft.com...
> > its the later, the columns in a table (one for each set)...is there
> another
> > way to do this?
> >
> > "Evi" wrote:
> >
> > > Just as a matter of interest, Adam, is this report based on a  Crosstab
> > > Query or do you have one column in your Table called Set 1 Reps, one
> called
> > > Set 2 Reps etc?
> > > Evi
> > >
> > > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > > news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > > > I have a report that contains a customer ID header, customer detail
> > > section,
> > > > and customer ID footer. In the detail section i have 4 fields that i
> am
> > > > applying an equation to, and performing that in a 5th field unbound
> > > control
> > > > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category Point
> > > > Value]). When i view the report it gives me the correct number for
> this
> > > > calculation (the 5th field name where this calculation is happening is
> > > called
> > > > "Text48"). In the footer section i am now trying to add up a total of
> > > every
> > > > record that shows up in the detail section like so =Sum([Text48]). I'm
> > > trying
> > > > to sum up all the detail records of Text48.
> > > > Its not working and the report does not recognize Text48. Can anybody
> > > help??
> > >
> > >
> > >
> 
> 
> 
0
Utf
3/22/2008 8:19:00 PM
If you use the query to perform calculations on a 'row', it makes it easier
to perform calculations in the report on the group footers and report
footers.

Evi

"Adam" <Adam@discussions.microsoft.com> wrote in message
news:4E97FBBB-35EE-4B60-9DDF-C02658A50291@microsoft.com...
> should it always calculate in the query instead of the rprt? so that this
way
> the rrprt does not have to run in order for me to get the value?
>
> "Evi" wrote:
>
> > I don't know what your db is about but it does sound horribly as if your
> > database is not designed correctly .
> >
> > What happens if you want to add a Set 5 Reps. Do you have to add another
> > field column to your database, forms, reports, calculations etc?
> >
> > Or have I completely misunderstood something.
> >
> > Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
> > query on which your report is based. (if it is based on a table,change
it
> > into a query by clicking next to Record Source, let the Query Builder
open a
> > query in Design View and add all the fields to your query grid.
> >
> > While in Design View, at the next empty column type, in the first row
next
> > to the other field names, type in:
> >
> > TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps]  *
> > [Category Point]
> > Check in the normal Datasheet view to see if its doing what you think it
> > should.
> >
> > Use the Field List button and drag  the TotReps field from  there into
your
> > report instead of the unbound text box.
> >
> > In your report and customer footers, you can now use
> > Sum ([TotReps])
> >
> > Evi
> >
> >
> > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > news:773D267F-75C5-40F1-BD87-F50216715988@microsoft.com...
> > > its the later, the columns in a table (one for each set)...is there
> > another
> > > way to do this?
> > >
> > > "Evi" wrote:
> > >
> > > > Just as a matter of interest, Adam, is this report based on a
Crosstab
> > > > Query or do you have one column in your Table called Set 1 Reps, one
> > called
> > > > Set 2 Reps etc?
> > > > Evi
> > > >
> > > > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > > > news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > > > > I have a report that contains a customer ID header, customer
detail
> > > > section,
> > > > > and customer ID footer. In the detail section i have 4 fields that
i
> > am
> > > > > applying an equation to, and performing that in a 5th field
unbound
> > > > control
> > > > > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category
Point
> > > > > Value]). When i view the report it gives me the correct number for
> > this
> > > > > calculation (the 5th field name where this calculation is
happening is
> > > > called
> > > > > "Text48"). In the footer section i am now trying to add up a total
of
> > > > every
> > > > > record that shows up in the detail section like so =Sum([Text48]).
I'm
> > > > trying
> > > > > to sum up all the detail records of Text48.
> > > > > Its not working and the report does not recognize Text48. Can
anybody
> > > > help??
> > > >
> > > >
> > > >
> >
> >
> >


0
Evi
3/22/2008 9:13:31 PM
You don't HAVE to do it but in your case.  it certainly will make life
easier.
If the calculation applies to a 'row' of data rather than a group of data
then I personally can't think of a case when it would work better in a
report than in a query.  (Any exceptions, anyone?)

Having the results in a query also makes it available eg in other reports,
in code, in forms and in queries based on this query.

Evi




"Adam" <Adam@discussions.microsoft.com> wrote in message
news:4E97FBBB-35EE-4B60-9DDF-C02658A50291@microsoft.com...
> should it always calculate in the query instead of the rprt? so that this
way
> the rrprt does not have to run in order for me to get the value?
>
> "Evi" wrote:
>
> > I don't know what your db is about but it does sound horribly as if your
> > database is not designed correctly .
> >
> > What happens if you want to add a Set 5 Reps. Do you have to add another
> > field column to your database, forms, reports, calculations etc?
> >
> > Or have I completely misunderstood something.
> >
> > Anyway, for now, do your [Set 1 Reps] + [Set 2 Reps] calculation in the
> > query on which your report is based. (if it is based on a table,change
it
> > into a query by clicking next to Record Source, let the Query Builder
open a
> > query in Design View and add all the fields to your query grid.
> >
> > While in Design View, at the next empty column type, in the first row
next
> > to the other field names, type in:
> >
> > TotReps: [Set 1 Reps] + [Set 2 Reps] + [Set 3 Reps] + [Set 4 Reps]  *
> > [Category Point]
> > Check in the normal Datasheet view to see if its doing what you think it
> > should.
> >
> > Use the Field List button and drag  the TotReps field from  there into
your
> > report instead of the unbound text box.
> >
> > In your report and customer footers, you can now use
> > Sum ([TotReps])
> >
> > Evi
> >
> >
> > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > news:773D267F-75C5-40F1-BD87-F50216715988@microsoft.com...
> > > its the later, the columns in a table (one for each set)...is there
> > another
> > > way to do this?
> > >
> > > "Evi" wrote:
> > >
> > > > Just as a matter of interest, Adam, is this report based on a
Crosstab
> > > > Query or do you have one column in your Table called Set 1 Reps, one
> > called
> > > > Set 2 Reps etc?
> > > > Evi
> > > >
> > > > "Adam" <Adam@discussions.microsoft.com> wrote in message
> > > > news:3F74580D-2785-46DE-BF0C-9A5772ACCC04@microsoft.com...
> > > > > I have a report that contains a customer ID header, customer
detail
> > > > section,
> > > > > and customer ID footer. In the detail section i have 4 fields that
i
> > am
> > > > > applying an equation to, and performing that in a 5th field
unbound
> > > > control
> > > > > =([Set 1 Reps]+[Set 2 Reps]+[Set 3 Reps]+[Set 4 Reps])*[Category
Point
> > > > > Value]). When i view the report it gives me the correct number for
> > this
> > > > > calculation (the 5th field name where this calculation is
happening is
> > > > called
> > > > > "Text48"). In the footer section i am now trying to add up a total
of
> > > > every
> > > > > record that shows up in the detail section like so =Sum([Text48]).
I'm
> > > > trying
> > > > > to sum up all the detail records of Text48.
> > > > > Its not working and the report does not recognize Text48. Can
anybody
> > > > help??
> > > >
> > > >
> > > >
> >
> >
> >


0
Evi
3/22/2008 9:23:37 PM
Reply:

Similar Artilces:

Page Footer sum Microsoft solution question
http://support.microsoft.com:80/kb/296249/en-us The above website gives a solution for sums per page but I cannot get any sums to work in the page footer. Are the instructions right? Has anyone else had problems with this? Michael wrote: >http://support.microsoft.com:80/kb/296249/en-us > >The above website gives a solution for sums per page but I cannot get any >sums to work in the page footer. > >Are the instructions right? Has anyone else had problems with this? The article is correct. Carefully, review it again and double check that you followed all of the s...

Criteria sum in report
I have a report looking something like this (just an example): Id Value 1 2 2 4 3 1 4 2 5 3 In the report footer I want a field that gives me the sum for values with id's between 2 and 4. Earlier I have solved this by making an extra query and a subreport, but I'm wondering if it could be solved otherwise. Maybee by using a sql sentence for the controllsource property on an ordinary textbox. Appreciate any suggestions -- regards hallgeir Try something like this in the Control Source of a text box in the Report Footer section: =Sum...

SUM of COUNT?
I have a report based on a crosstab query that counts the number of enquiries from an advertising source for a year and sorts them into months So columns are by date and rows give the source and the value is the count of enquries in the source. I want to total the number of enquiries in the month on the bottom of the report. I have tried putting a text box in the report footer that uses the value field and set it's running sum property to over all but it just gives the sum for the source and not all the sources added together. Is it possible to do this? Peter One of the f...

CountIf and Sum Help???
Within a macro I am trying to add 2 formulas to the bottom row of my worksheet. The code is below and has allowed me to add static data. Now I need to do a count or countif (or something else)for column c that will give me the total number of rows that are "01". And for the final column i want to do a sum. Can anyone help? 01 150080380549 0000718649 000000095556 01 150080380549 0000718650 000000012000 01 150080380549 0000718651 000000002080 01 150080380549 0000718652 000000002912 02 150080380549 ???? ??? ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Off...

sum of blank and non blank
Hi, How do I count blank cells in a range which contains text and separately how do I count cells that are not blank? I used =countblank(range) which works but how do I do the opposite, count cells WITH text in range? Tx, S =COUNTA(range) will count all cells that are not empty in "range". =COUNTIF(range,"*") will count only cells that contain text. HTH Jason Atlanta, GA >-----Original Message----- >Hi, How do I count blank cells in a range which contains text and separately >how do I count cells that are not blank? >I used =countblank(range) which work...

Sum of 3 maximum numbers
I have 12 cells with some numbers. How can I calculate the sum of the three maximum numbers among them? Something like: =SUM(LARGE(A1:A12,{1,2,3})) Ruda wrote: > > I have 12 cells with some numbers. How can I calculate the sum of the three > maximum numbers among them? -- Dave Peterson Just for fun =IF(COUNT(A1:A12)=0,"",SUM(LARGE(A1:A12,ROW(INDIRECT("1:"&MIN(3,COUNT(A1:A12 ))))))) as an array formula, so commit with Ctrl-Shift-Enter. This caters for less than 3 numbers, without an error -- HTH RP (remove nothere from the email address if m...

sum doesn't update total without double-click of cell
In a spreadsheet, using formula like "A1 + B1" to get the sum of values in cells A1 and B1 to show in a third cell. The total in the third cell does not update automatically after changing the value in one of the two cells (e.g., A1). Only after double-clicking the third cell, is the proper total displayed. Any ideas why this is happening? Set your calculations to automatic under tools>options>calculations -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "David E. Gross" <David E. Gro...

I need to sum up some values in a column of a query
I am sure the answer to this is easy but I cant seem to get it. I need to sum a few columns. I have tried using a total query but I dont have anything for it to group by. I tried putting in a variable like Total:"" but that didnt work. All of my other queries have had tables which I could LEFT JOIN to create my row names but now I just want the totals. Thanks for help. You can sum without a group by. -- Build a little, test a little. "dannie" wrote: > I am sure the answer to this is easy but I cant seem to get it. I need to sum > a few columns....

Use of the DSUM function
Who can give me a hand? I am using the function below; so far so good. DAvg("[wng_OPP_SLPK1]";"TBL_Woningen";"[wng_CFT Code] = """ & [pdt_CFT code] & """") Now I need to generate a sum on a field that contains the value "J". How can I change the function: Sum(IIf([TBL_Woningen]![wng_COD_INDCV]="J";1;0)) into a DSum function where the field to sum is wng_COD_INDCV (with the text "J"), the table TBL_Woningen and the criteria [wng_CFT Code] = """ & [pdt_CFT code] & "...

cross tab column sum
Access07; haven't used CrossTab queries before; initial set up is fine; weeks along top, employees are rows, values in grid ok... the wizard allowed me to easily sum each row; which I need how does one sum the columns? there are 52 weeks and so am hoping to avoid a manual coding approach for each... figured it might be a common question but have searched around on this site and haven't come up with that q/a... tia -- NTC You can't in a query unless you use a union query. Are you using the crosstab query as the source for a report? If so, could you use a subrep...

Preserve number as text in Pivot table sum of values field
Does anyone know how I can create a table like this: first column: second column: third column: (product name) (product number) (sum of products) I know how to create the first column (via "row labels") and third column (via "sum values"). But for the second column, if I put the fields under "row labels" the names get indented under the product name. If I put the fields under "sum values" the names gets counted. I'd like to preserve the product numbers as text. Thanks in advance fo...

SumProduct using greater than
I have the following formula =SUMPRODUCT(('All Resource Plan'!$B$3:$B$42=Principal)* ('All Resource Plan'!D$3:D$42>0)) Where B3 to B42 on the all resource plan tab have a variety of roles including principal. On the same tab d3 to d42 is a range of numbers from -1 to +1. I need to sum all values associated with a role that are greater than 0. I was using this formula with an =1 and it worked fine, however when I changed it to >0 it no longer works. Any help would be appreciated. First of all, you are counting not summing, secondly the formula works for me....

Sum of 6 moving cells
Good day to all, Sheet1: Column A is the date (from Jan 1, 2005 to the current date), Col B contains the hours worked on that date and Col C is hours worked for the last 12 days. I manually type in the hours worked everyday on Col B and there is a formula on Col C to add the last 12 days. There's about 45 units that I keep track of the hours worked. How can I show on a summary sheet, the hours worked of each unit as of yesterday? My problem is that the cell that totals the last 12 days moves down one cell everyday. -- ledzepe --------------------------------------------------------...

Can I Sum by Cell Colour or Format?
I have a large list of values, these values have been colour coded. I would like to sum each of the colours seperately. I know i can add a filter, sort by colour & sum this for each coulour, but am wondering if there is a formula that would do this for me? Ashley, have a look here http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ashley" wrote: > I have a large list of values,...

How to sum the 3 best scores for every country in a contest?
To sum the 3 highest numbers, following functions can be used in a really nice combination: =SUM(LARGE(A1:A100, {1,2,3} ) ) But how to add an condition to it? Example: Column A below are the scores for induvudual persons in a contest. Column B below is the country they are competing for .. How to add the sum of the 3 best scores for each country ? The correct answer should be 213+199+187=599 for USA and 198+158+135=491 for Canada. A B 141 USA 199 USA 135 CAN 187 USA 158 CAN 145 USA 213 USA 198 CAN 120 USA 180 CAN Reall...

Excel 2003
When I type a formula like "@sum(f1..f24)" my cell is created with a hyperlink to an email address, brings up Outlook, is really annoying. I can remove the hyperlink of course, but it's a real pain. I haven't been able to find a reference in the Help file. I did not 'turn' this feature on, so I assume it's a default. Thanks for any help :) Lous, You are using Lotus 123's syntax, not Excel's. Type in the formula =SUM(f1:f24) or use Tools / Options... Transition tab, and check "Transition formula evaluation" HTH, Bernie MS Excel MVP ...

help with auto sum
This is what I am doing: =SUM(C69:J69) in K69 in C69 I have .10 in D69 I have 5 in J69 I have .25 K69 is giving me a total of 0.25 which is incorrect. I have tried formatting cells to text/general and number but it still wont add proerly. I have also set to 2 points after decimal place. I have used auto sum and manually typed in the formula to no avail. Any ideas on what is wrong. thanks try this once more: Select D69 Change the format to General (make sure it's not Text) Then re-enter the value. Then do the same for C69. (I'd do it for each of the cells.) It's not ...

Sum all values using IF function
I have a list full of information on my sheet. One column displays a department number and another column displays a number of hours that this department has worked. I need to sum together all the hours of a certain department to get an overall total. e.g. Dept Number Number of Hours 151 4 151 4 151 1 132 6 132 2 151 4 144 8 144 3 132 1 How do i sum all the numbers of hours for dep...

How do I summarize records in Access?
I have a table that has upwards of 70,000 records. I am wondering if there is anyway that I can summarize this information so that it will contain less records but so that I will still be able to see all of the detail. For instance, I would like to create a table that would summarize data by city (I am looking to summarize numeric information so calculations can be done) so that I will still have all of my columns in tact but instead of 100 rows for each city, I will only have one. Any help would be greatly appreciated!! Hi Cory, This is what queries and reports are for. For example ...

total sum of circle diagram
How can I get the total sum of a circle diagram which shows the single results in relation to the total sum to be displayed? Thanks in advance You mean a pie chart? - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Schley" <Schley@discussions.microsoft.com> wrote in message news:6C5CE1C4-F4B5-45ED-ADAD-EC6405CC59D9@microsoft.com... > How can I get the total sum of a circle diagram which shows the single > results in relation to the total sum to be displayed? > > Tha...

Sum formula #4
Hi Kids of Excel, just a little help please, what formula should i use to add a range o cells up. i have hidden a number of cells and want to add up the range which i shown how do i do this (it can be alot of data sometimes)? Thanks in advanc -- paulu http://www.frontlineuk.co ----------------------------------------------------------------------- pauluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=520 View this thread: http://www.excelforum.com/showthread.php?threadid=27360 Hi if hidden by a filter try =SUBTOTAL(9,A1:A100) "pauluk" wrote: &...

cell reflect an amount when "C" is typed in, then sum last cell
3/30/2009 1 2 3 4 5 - 31 row five service charge row six Mowing $45.00 [ ] [X] [ ] [ ] [ ] ---[ ] [$45.00] row eight This is basic what I have, I want to be able to select any of the days of the month in the aray and have that amount to show at the end when I select that date with an X. In row five the only thing that changes is the date...changes with the current date. In row eight the only thing I want to happen is to give a pre-set number at the end and if I select several more dates I need them to su...

Worksheet not displaying "Sum" in lover right corner
"Sum" is selected, but it is not displaying in the lower right corner of the spreadsheet. Please advise how to bring the display back. Right-click in that part of Excel, and check the Sum option. "LMRS1" <LMRS1@discussions.microsoft.com> wrote in message news:C7D74A67-E3AC-45C4-8390-E234BD7E7751@microsoft.com... > "Sum" is selected, but it is not displaying in the lower right corner of > the > spreadsheet. Please advise how to bring the display back. Did you select at least 2 cells with numerical values ? (one, or more of the...

Summing with Spaces
Hi, If I have a column of numers like this: 3 4 4 3 2 1 3 2 Each group of numbers represents a different day seperated by a space. In the adjacent column I want to do a running total of each group so I have a running subtotal for each day, so it would look like this: 3 3 4 7 4 11 3 3 2 5 1 6 3 3 2 5 The formula looks complicated. Thanks for any help. Make sure the first line is empty I assume that with "a space" you mean an empty row. If your data is in Column A (starting in a2), put this formula in B2: =IF(A2="",0,B1+A2) And copy down as far as you ne...

Sum/Total Value of All Contracts
Hi, We currently have a parent customer, with multiple contracts. At the parent customer level, we would like to write something that automatically calculates the sum of all contracts for the customer. Has anyone done this? If so, can you please kindly shed some light as to the easiest solution around this? Thanks in advance. Hi, We have done this before and Callouts seems to be an elegant solution. A post callout on contract could update the custom field on Parent Account for calculating the sum. Hope this helps! Sam _______________ Inogic Innovative Logic Innovative solutions for ...