I have two tables: Measure and Score. The measure table is the parent table containing details for various performance tracking. The Score table contains the monthly scores for the respective measure. I've uploaded some screenshots to help with my inquiry: http://www.flickr.com/photos/9731133@N07/sets/72157600672829561/ I need two separate controls for a Dsum of the numerator and denominator for each individual measure which I can then ultimately use to calculate a performance rate. My current Dsum calculation (which I know is wrong) uses the entire Score table as the domain which pe...

This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3F703.3739C000 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can someone tell what I need to solve this problem? On sheet1 I have a list of records that have many rows for each subject. = Eg. A B C Name ACCT AMT =20 Bill 1101 $ 12.00 =20 Bill 1102 $ 45.00 =20 Bill 1103 $ 22.00 =20 Jeff 1101 $ 10.00 =20 Jeff 1102 $ 80.00 =20 Jeff 1103 $ 17.00 =20 On she...

Hi, I have data as described below: AA1 BB1 CC1 10 EE1 AA2 BB2 CC2 8 EE2 AA3 BB3 CC3 4 EE3 AA4 BB4 CC4 5 EE4 AA5 BB5 CC5 10 EE5 AA6 BB6 CC6 20 EE6 AA7 BB7 CC7 5 EE3 So, basically I have subgroups like above in an spreadsheet. The number of rows in each subgroup are not fixed. It may have any number of rows. The only way I know is the subgroup is ended is when I encounter a blank row. I need to insert a new row at the end of every subgroup and then sum up column D in this newly inserted row. So, for the group 1 I should have a sum of 27 (10+8+4+5), and for second sub group ...

I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...

=SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") Here is what I am trying to do: If D3 = AL3 then I want it to return the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 I know it is something simple I am missing ....... Just use If() =if(d3=al3,H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3,0) JPD wrote: > =SUMIF(D3,AL3,"=H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3") > > Here is what I am trying to do: If D3 = AL3 then I want it to return > the sum of H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3 > > I know it is something simple I am missing .........

I have been given a data set which contains work hours and dates in the form of mm/dd/yy. I am trying to sum all the hours worked in each month. A co-worker suggested I use sumproduct but I'm still not able to achieve the process. Additionally, the data crosses over years, so I am not quite sure how to create a fomula that takes the year into account. Any help would be great. Thanks much. -- Zachary Baker Using a pivot table grouped by months and years would be the most straightforward. "Zachary Baker" <Zachary Baker@discussions.microsoft.com> wrote in 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 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...

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...

Hi All, Hope someone can help me with this. I am trying to find a method of finding the first blank row on a worksheet and perform a sum calculation for column B: example: Before A B ww ww x 1 x 2 x 5 x 6 After ww ww A B x 1 x 2 x 5 x 6 14 I have code to find the first blank row: Dim NextRow As Long NextRow = Range("B65536").End(xlUp).Row + 1 Cells(NextRow, 2) = "test" But of course, I don't want it to say test, but insteade calulate the sum of all values in the column from B2 down (...

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...

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 --------------------------------------------------------...

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...

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...

I am trying to get one cell to return the value of an entire column on another worksheet for which the last row is always changing. If I use: =SUM(Sheet1!$A$2:A25) Then the cel will be broken once I add a row 26 to Sheet 1. What I would like to do is create a sum of the column no matter how many rows there are. Thanks. "Mike F." <nospam@spamless.com> wrote in message news:4coomvgodg9lvcp66cp9bddcrnhov8ap00@4ax.com... > I am trying to get one cell to return the value of an entire column on > another worksheet for which the last row is always changing. If I use: >...

Hi All First of all THANK YOU for all the great posts... has been a great hel for me... Uptill now I have always been able to find as answer, bu this one I cant find. Hope you can help me out Question: I have made a PIVOT table and via the %-of totals I get the % per ro of the analyzed datas. What I want is a automatic SUM of th percentage, so that the final row adds up to 100%... Hope yo understand what I mean I know I can manually make another line besides the Pivot, but as m laziness I would like to have it done automatically.... Regards Martin (Denmark -- Message posted from http://www...

Is there a way to calculte the sum of two given times? For example, A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9". Also, if it can be done, because my time is always based on quarter hour, will the end result show a fraction? Example, A1is 07:45, B1 is 17:00, then C1 is 9 1/4. << Is there a way to calculte the sum of two given times? For example A1 says 08:00 and B1 says 17:00 then C1 will auto calculate "9".>> =(B1-A1)*24 and format Cell C1 as *_general_* << Also, if it can be done, because my time is always based on quarte hour...

Hi, The Excel pivot table gives me almost all I want to organise some data. However, I get "subtotals" for every instance of a particular "row category". So if the category is "Green" and there are three instances of this in original data, pivot will sub-total these before listing the rows of category "RED". I only want totals for the whole table, not sub-totals "within" the table. Can I control this behaviour in the pivot table? Currently I do some "manual post-processing" using filters - but it is not dynamic and if th...

I am trying to numbers from different cells and add them for a total and then multiply that number by a percent to get my answer. I can't figure out how to make this formula. Example c10+d10+e10+f10 +j10+m10 and then that total *by a percent. Thank you! =(c10+d10+e10+f10 +j10+m10)*10% -- Kind regards, Niek Otten Microsoft MVP - Excel "bic424" <bic424@discussions.microsoft.com> wrote in message news:CE16BB7B-9880-478B-9099-5309788A25BD@microsoft.com... >I am trying to numbers from different cells and add them for a total and >then > multiply that number b...

Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

HII have a table with commitments of customers on how much they will pay on what date. The fields are customer id, date and amount.Need to make a query to list out customer id, total payable till current date. I tried using sum function in total row in design grid, but it gives me some wierd result. other functions like avg, max and min work fine.also where can i specify that records with future dates are not to be included in the total?Thanks for any helpRamesh ...

Hi world, This is my log (20k) <http://img2.freeimagehosting.net/uploads/ 32373110dd.png> I have been trying to adapt a dynamic chart tutorial from Jon Peltier into a Excel filetransfer log. Dates along the x-axis and the sum of files transferred per day along the y-axis. Only problem is that I cant figure out how to plot the sum of total files transferred pr. date. In my log I have multiple entries for the day 23-11-2007, but instead of a total file count for the whole day Excel only use the last entry (A16). This is my named ranges: AllDates: <=Data!$A$1:INDEX(Data!$A:$A;MATCH(...

Simple query that's probably been asked a million times before so it might be easier just to point me in the direction of a website that deals with Excel functions for dimwits. But my query is this.... I have a simple spreadsheet with the following columns : Date (A), Description (B), Income (C), Expense (D), Balance (E). I want a summary of it all at the bottom of the sheet so that for a row entry of "April 2008" it will look to see all the income entries in April 2008 and total them up. Similarly in the next column on the same row there should be a total for expenses fo...

I need help with creating a formula. i have 2 columns in my data i need to sum in my chart, 1 column is the sales reps name the other column is staus. when a project i work on becomes an order i type the word "order" in the status column, but i cant get the 2 columns to add up in my chart. what im trying to do is every time a given sales reps name appears in one column and the word "order" appears in the other column in the same row. i need to have a sum of how many times this appears, this gives me the amount of orders any one sales rep has generated. any help wou...

Hi, In Cell AB10 I need a formula to sum the following cells inthe same line. The cells are: D, F, H, J, L, N, P, R, T, V, X, Z Is there a way to do this other than using: =d10+f10+h10........+z10 (sum of every other cell!!) =SUMPRODUCT(--(MOD(COLUMN(D2:Z2),2)=0),D2:Z2) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Khalil handal" <khhandal@yahoo.com> wrote in message news:up25YVaXIHA.280@TK2MSFTNGP04.phx.gbl... > Hi, > In Cell AB10 I need a formula to sum the following cells inthe same line. > The cells ar...

How do I sum e.g. only green cells in my worksheet, when I color the cells in two different colors, green and red? Hi see: http://www.xldynamic.com/source/xld.ColourCounter.html and http://www.cpearson.com/excel/colors.htm not possible without VBA -- Regards Frank Kabel Frankfurt, Germany Jan Caesar wrote: > How do I sum e.g. only green cells in my worksheet, when I color the > cells in two different colors, green and red? try Sub addcolorcells() For Each c In Selection If c.Interior.ColorIndex = 4 Then ms = ms + c Next MsgBox ms End Sub -- Don Guillett SalesAid Software donald...