# Create Sub Totals And Sum Total

Jan 27, 2010
Attached is an example of the workbook I am looking for: A macro will take the sheet "Data" and split the info across into new separate worksheets labelled "SIM","MOR","RED","CAR","EMB". The split worksheets contain the same info as "Data" however the column for "Total Arrears" is sorted as descending and the moment values become negative, there is a 5 row gap to seperate the Positive amounts and Negative amounts in a descending order

What needs to be completed:

I need to create Sub Totals in columns E:K for both the negative and Postive balances (and Label them as "Sub - Total" in column D) as well as have them represented as a percentage, and then after that have a grand total value represented at the bottom. I have attached examples of what the final product should look like for each worksheet (they are labelled in blue)

The problem I have is that I am unsure of how to use VBA to recognise where to put the amounts for Sub Totals. I would assume it would use a .End.xlUp.Row and some kind of Offset, but I don'r even know where to begin. The "Data" sheet has a button that runs the macro to split the data into worksheets, but the worksheets SIM/MOR/RED/CAR/EMB must be deleted first since there cannot be duplicate sheets (which is why there is also a button to delete those sheets)

View 2 Replies
ADVERTISEMENT
Aug 7, 2006

Trying to create an excel chart to create totals based upon different keys. I

need to be able to calculate how many customers there are by Manager and then

By Rep. Then to figure out how many were New, Current, Total # of RSVP and

attended for that Rep. Below is how I have started but I am having some

problems getting certain parts. I know when I get one the rest will fall into

place. I can calculate how many total customers by manager and by rep just by

doing a Countif command but how do I determine the # of New, Current etc. Is

there a If Than command? Managers Totals are simply his reps totals.

Example..

A B C D E

F G

1 Manager Rep Customer New Biz Current # RSVP # Actual Attend

Need totals to look something like this....

A B C D E

F G

1 # of Cust # New # Current #RSVP #

Actual Attend

2 Manager

3 Rep 1

4 Rep 2

View 15 Replies
View Related
Mar 5, 2014

I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.

View 3 Replies
View Related
Mar 21, 2009

I am a bit stuck with an excel spreadsheet I do for work and would like to ask your advise, I will try to explain as best as I can.

I am running a small store that feeds 13 buildings.

On the spreadsheet there are 13 sheets, one for each building.

The items will go out monthly.

What I need to do is record the total items from each sheet in a column called All Totals on the first sheet, this will add up the totals for all the buildings.

If I put an attachment that may help out a bit.

If you can look at P3 it is all the P3s to add together, next down will be total of P4,P5,P6.

View 2 Replies
View Related
Oct 2, 2009

I have to keep a record of the running totals of school house points for each week. The problem is that teachers are very lazy and don't record data every week so I have many blank cells which my current formula can't cope with. I've tried using N/A but it doesn’t seem to work? (Have thought about threats of violence but would probably lose my job) I’m if there is no data (blank or 0) then I need it to keep the same total in the cell as the previous week and so on until new data is entered and updates the total. I have attached a simplified copy: Teachers enter points in the HP sheet, the Running Totals sheet (TAB) contains the formula.

View 4 Replies
View Related
Jun 17, 2013

I have a spreadsheet with 13 sheets. Sheets 1-12 are Jan - DEC and I would like to make the 13th sheet a totals sheet Year to date.

The totals sheet will update when I populate/update the fields in sheets 1 -12.

The sheets have names in column A.

The sheets have labeled columns in Row 1.

What I am trying to accomplish is something like "if BOB in A:A on sheets 1 - 12, sum (or average) column B on the same row as BOB for sheets 1-12 and add to sheet 13.

I know this doesn't work, but this is my thinking =SUM(JAN:!H2:H200, FEB!H2:H200, etc.) if A2:A200 is "EAXACT MATCH" to sheet 13 A:A

I want to be able to sort/filter/hide on different sheets at different times without affecting the totals sheet. do I need to use subtotal?

Tech

Total upgrades

Total Repairs

[Code].....

View 4 Replies
View Related
May 20, 2014

I am trying to do an automatic input with my daily total sales.. i did it in total but my problem is i don't know how to make the date change.

View 2 Replies
View Related
Jan 2, 2009

In column A I have dates, in column B I have data.

What I am trying to do is get the monthly totals from the data so instead of:

01/02/08 - 52

06/02/08 - 87

14/03/08 - 23

12/13/08 - 12

I would get:

Feb 08 - 129

Mar 08 - 33

View 6 Replies
View Related
Jun 19, 2014

I am trying to create a simple dash board using only Excel. What I am trying to do is I want to create sum of shift total of whole month.

Output

Total

Total "A"

Total "B"

Total "C"

from Below table

DateShiftJob DescriptionProd AProd BProd C

1-Jul A

1-Jul B

1-Jul C

2-Jul A

2-Jul B

2-Jul C

TILL...End of month

View 2 Replies
View Related
Dec 3, 2008

I am trying to do is create a total of only the last 90 days of entries from a list of $ amounts. Attached is an example. We are going to start keeping track of our cashiers variances but at the top of the sheet we only want to know what their last 90 days of entries equal.

This would be considered a rolling 90 days, so older variances drop off after 90 days, they would still be left on screen but would not effect the main total.

View 4 Replies
View Related
May 22, 2014

i have created this spreadsheet for windows and doors. My only problem right now is that when i put a quanity of "3" in the correct column with frame removal. it doesn't multiply it by 3.

Example:

90x90 = 56.25 sq ft (window) : 3 QTY .. with frame removal should equal $ 855 but its only adding the 1 frame removal so its $785

Frame removal, jamb ext and casing should multiple the value from the table worksheet with the QTY of windows.

View 2 Replies
View Related
Sep 17, 2009

Hi Guys,

im really stuck and could use one of your expertise.

i have an excel spreadsheet with 65,000 lines of data

i have 9 Coloums in the the file

Despatch DateCustomerDseqOrderLineItemPost CodeVol m3Gross Kg

13/02/09jason00002029095R055TR4 8QQ0.1099280.3313/02/09jason00002034741R043TR4 8QQ0.31621218.313/02/09jason00002034742R064TR4 8QQ0.8793673213/02/09jason00002034743R045TR4 8QQ0.94863684.9

i need a line here to sum it before the next order starts?

13/02/09jason200002033141A157WD18 7QX0.02832113/02/09jason200002033142A096WD18 7QX1.609288213/02/09jason200002033143A125EJAWD18 7QX0.849646.813/02/09jason200002033144A09JJAWD18 7QX0.13732102.8

im trying to create a formula that will sum each order on another new coloum which i will create called total weight

i need it to search the date then the customer name then the gross kg and total them up as there is multiple orders

View 9 Replies
View Related
Mar 5, 2012

Let's say I have a list of 200 customers with their business name in column A and their total 2011 purchases from my company.

How can I create a report to show the TOP 20% based on sales to grand total.

Basically, of my total business last year... I want to see the top 20% of this customer list that contributed to my total business.... and so on... to show top 40%, top 60%, etc. So, did 5 customers contribute to 20% of my total... or was it 10, 15?

Let's start with the above 1st.... but I want to also know if I can make this report (or maybe dashboard) dynamic? I'm pulling my sales from a database, and want to be able to refresh this report periodically throughout 2012. The list of customer will grow throughout the year too.

View 3 Replies
View Related
Jul 26, 2012

I have a column for suppliers names and a total column The suppliers names differ. How can I create a separate column to show the total of spent for each supplier. I'm not up on excel code

View 4 Replies
View Related
Jan 7, 2010

I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.

Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.

I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.

View 2 Replies
View Related
Apr 22, 2009

This may not be the best way to do this, but I don't know Macros or Pivot Tables.

I am looking for a way with formulas to do the following:

Within a workbook the 1st sheet is the data entry.

In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:

Example:

Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.

G2:G2999 in the same sheet is where the data is.

Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?

View 9 Replies
View Related
Dec 21, 2008

I'm sure this can be done but I don't think I have the formula correct. What I am trying to do is add each day's total while displaying a positive number if the total exceeds 6000.

View 5 Replies
View Related
Dec 13, 2013

number for item detail level from category standpoint. For example, in the category sheet has total number per category and per month and in the item detail sheet has a list of item number with category. I want a formula to recognize which item belongs to which category then use the total number of category by month to multiply with % of total in column D from Item Detail Spreadsheet.

View 2 Replies
View Related
Jun 13, 2014

I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.

been trying to figure out a macro so when you press the update button it then just updates the monthly total.

Colac Production.xls

View 7 Replies
View Related
Oct 10, 2009

In an excel work shift schedule, I am trying to total the total number of days someone is scheduled for different shifts. I can get a total for the current schedule but I want to be able to have a year to date total as each new schedule is added in. These totals are in the AF through BC columns in the xray shift totals in the attached worksheet.

I don't know if it is possible or not and am working on a pre-existing worksheet.

View 9 Replies
View Related
Sep 13, 2009

Sub sumbotton()

Dim ar As Range

Dim rng As Range

Set rng = Selection.CurrentRegion

Set rng = rng.Resize(rng.Rows.Count + 1)

rng.Rows(rng.Rows.Count).Select

For Each ar In rng.Areas

ar.Resize(1).Offset(ar.Rows.Count) = "=SUM(" & ar.Address & ")"

Next ar

End Sub

View 9 Replies
View Related
Oct 15, 2007

I have a sheet in my workbook with at least 180 small tables, there may be more.

I woulds like to be able to change total formulas for all tables at once to show either year-to- date or total year.

For example:

If we have only progressed through the second period of the year, I would like to choose something to indicate period 2. At other time I may want to know the total year whether the periods are completed or not.

View 9 Replies
View Related
Jun 9, 2014

I have a column with Cells that will sat True or False, the amount of rows will be different every time, I need to work out what the total percentage of True cells compared to the total number of cells. How would this be achieved.

View 9 Replies
View Related
Oct 9, 2008

Here's what I have...

Column A Column G

date $ Amount

my data example...

10/5/2008 $10.00

10/5/2008 $20.00

10/8/2008 $12.00

10/8/2008 $8.00

10/8/2008 $25.00

10/9/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts.

So the result would be...

10/5/2008 $30.00

10/8/2008 $45.00

10/9/2008 $75.00

View 4 Replies
View Related
Jun 5, 2014

Here is what I have:

cell b6 =SUM(B3:B4)

cell b8 =C8*B6

cell b20 =SUM(B11:B18)

cell b29 =SUM(B22:B27)

I want to add all these cells together for the total. I used the formula =SUM(B6+B8+B20) and it works fine. However, when I try =SUM(B6+B8+B20+B29) I end up with 0.00 in cell B31

View 2 Replies
View Related
Nov 22, 2012

I'm tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?

View 3 Replies
View Related
Jan 2, 2014

I've been trying different combinations of "If" statements to get a sum for the total items in house or total items at outside vend.

I need something that will be able to differentiate from parts in house to parts outside vend and put the total sums into M2 and M4

Please see the attached sample : Sample Qty.xlsxâ€Ž

View 2 Replies
View Related
Oct 9, 2008

Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...

Column A Column G

date $ Amount

my data example...

10/5/2008 $10.00

10/5/2008 $20.00

10/8/2008 $12.00

10/11/2008 $8.00

10/12/2008 $25.00

10/13/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...

Week 40 $50.00

Week 41 $100.00

View 3 Replies
View Related
Jul 9, 2009

I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.

View 6 Replies
View Related
Oct 20, 2009

This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.

View 14 Replies
View Related