# Sums Starting Over After Zero

Feb 25, 2009

I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.

## Sums

Feb 21, 2009

Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?

## Ifs Ands Sums

Feb 21, 2008

I have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name

What I want to do is sum the vacancy (in sqm) for each building.

Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1

Then look just at those rows and select those tenants named as "vacant"

Then add the areas from column B of those vacant plots.

## Average Of Sums

Feb 17, 2010

In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.

## How To Do 2 Sums In One Cell

Dec 10, 2013

I have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.

So a calculator I would simply type in 900 x 12 / 52 = 207.7

On excel I tried:

=sum(a1*12) /12

and it didn't work....

## Refresh All The Sums

Feb 12, 2009

I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.

I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.

I have been working on this spreadsheet for weeks and can't get past this part!

## Using Sums In Macros

Jul 26, 2006

I made a budget for my project and have to include a macro. I wanted to have the macro pop up a box for the user to input the expense they wanted summed for the year (student loan, car payment, etc) and then have the macro sum the payments from each month. I couldn't figure out how to do the pop up box either, so figured I could just do multiple macros (20 or so) but then ran into the added difficulty of needing to only use one cell out of every four.

I have columns for budgeted, actual, monthly varience and year-to- date varience and only wanted to sum the actual.) I have read the thread about summing every nth, but trying it without the macro didn't give me the same answer as adding each on a calculator. Also, trying to record a macro of just adding cells didn't work (I figured it probably wouldn't, but I tried anyways.)

## How Do I Put 2 Separate Sums Into 1 Cell?

Feb 2, 2009

I have a listing of numbers in different catagories such as the following:

Type A:
1

Type B:
2

Type C:
4

Total:
7

I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"

Type B:
"(2/7)"

Type C:
"(4/7)"

with that exact formating.

## Insert Rows With Sums Using VBA?

Feb 12, 2013

create a macro that would insert three rows with the first row summing the values in the lower two rows in columns E to J. The third row will get a cell formatting of black fill. In addition I would like the three rows grouped with the sum row showing when the group is closed. When the macro is executed the rows will get inserted below the last row in the sheet that has a cell format fill of the color black.

There is data in rows below the last black filled row so the inserted rows will be between that data and the exiting black filled row.

Perhaps it would be useful to explain how the designer has set up this worksheet and how the users are utilizing it. After a group of rows is created or new groups added, to insert an additional row within an existing group the user selects the black filled row at the bottom of the group and inserts a new row. This would ensure the subtotal at the top row of the group captures the values in the new row because the sum formula includes the black filled row. The black filled row also serves as a separator for each group. How to improve the design and function but the creator and users are not so open.

## A Sum Formuala That Adds Up All Sums

Sep 4, 2008

Is there a formula or a function that adds up all of the sums in a row.

******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA14=ABCD14603 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

## Delete Rows Where Row SUMS To Zero

Feb 5, 2008

I have a spreadsheet with 14 columns assigned to different regions. I need to remove all of the rows that have the contents of the rows under these columns as £0.00. In psydocode, the requirement is:

If Columns C:O = £0.00
Then delete

It's important that it only deletes the row if all the entries through C to O are £0.00.

## SUMS Of (start:end) Arguments Not Working

Jan 14, 2014

I have a ('start:end') function that's not working. A simplified example is shown below in the screen shots.

I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.

I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!

sheet1.png
sheet3.png
sheet4.png
worksheets.png

## Conditional Sums Using Multiple Criteria

Feb 2, 2009

I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. see attachment!

## Cumulative Sums A Formula Or Function

Oct 30, 2009

I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.

## Excel Time Calculations Sums?

Nov 28, 2013

i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.

## Auto Increment Sums Incorrectly

Jan 16, 2014

If I enter 1 in a cell, 1.1 below, select both and drag down, I should get a vector: 1, 1.1, 1.2 etc.

However, in the most recent instance of seeing this problem, at 6.5 I get 6.50000000000001! 6.6 onwards is then correct, at 7.2 the value is again incorrect in the 14th decimal place, and the errors continue intermittently through the sequence.

Is there a fix for this? It's extremely time consuming to have to check each auto-incremented array like this.

Oct 27, 2009

..............

## Multiple Sums Depending On A Description

Dec 28, 2005

I really don't know how easy or difficult it might be, but here it is:

I have something like this (example) :

--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------

I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF(\$B2="W2",\$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)

## Sums The Values In A Range Only To The Date

Dec 19, 2008

Need to write a formula which sums the values in a range only to the date we mentioned. For ease sample date & requirements are highlighted in attached spreadsheet.

## Pivot Chart - How To Show All Sums

Nov 6, 2013

I have a pivot table/chart that I need to show the sums as top 20 (or any other number) and All others. How can I do this.

I have an example file here: [URL] ...........

## Months And Quarters Sums Not Equal?

Jun 26, 2014

My list has dates. I am using this formula to determine the quarter for the date: ="Q"&INT((MONTH(S2)/4)+1).

As well I am using a formula to determine the month for the date: =E2-DAY(E2)+1.

In my book I have some sheets showing totals by month, in other I total up by quarter. I am experiencing a variance between the quarterly total by months and the quarterly total by quarter.

So I thought I'd try this formula to reconcile the two--but no success: =SUMPRODUCT((System=\$B5)*(Created>=Lists!M\$2)*(Created

## Compiling Large Sums Of Data

Oct 25, 2009

I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.

What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.

There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.

This sounds like a complex function to me but I am hoping that someone has a solution for me.

## Finding Sums Of Different Numbers Of Cells

Jun 21, 2006

I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.

## Calculate Sums, Multiple Criteria

Sep 2, 2007

I have a worksheet of hockey player statistics that I need to tally the Career Totals and the Totals for One Specific Team. Since each player has played a different amount of years in both their career and by team I cannot use a simple Sum formula as each player will have a different amount of rows to calculate and the rows may not be congruous.

The statistics also use text "--" to specify when a player did not play and "0" for when they did play but no statistics were registered. When the totals are calculated, I need to know whether they were registered or simply didn't play (i.e retaining the "--" in the totals instead of "0" if they did not play in those specific years). One example is listed, while the rest need filling in... attached is a partial sample of the data

## Hide Rows Across All Sheets Where Row Sums To Zero

Jan 23, 2008

Is there a way to append this code to accomidate accross all sheets?

Sub HideRows()
On Error Resume Next
With Range("e4:e34")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

## Get The Collective Sums For Each Day Of The Week Using Pivot Table

May 29, 2009

I have raw data with date & days of the month .

From this raw data I want to get the collective sums for each day of the week using Pivot or any other means - I tried Pivot but propabaly due to lack of my understanding of the same the desired result was not recived

## Selective Sums: Trying To Total Different Colored Cells???

Sep 17, 2009

i have a "budget" worksheet and am trying to total different colored cells and want to know if this can be done. attach a sample.

## Function That Sums The Values In A Range In Excess Of 2

Jan 14, 2009

I am trying to write a function that sums the values in a range in excess of 2. For example, if the cell is 3 add 1, if it is 5 add 3 etc.

Here is what i have so far, it compiles with no bugs, but it does not work in my worksheet. I wrote it in Sheet 1 (VBA) of the worksheet i am using it in.

## Automatically Adding New Worsheets To Existing Sums

Nov 20, 2008

In the attached workbook I have 3 sheets. The first 2 with the names starting with WO are individual sheets based on a single scaffold rental. The "Standing Equipment" worksheet is adding the components from the other two worksheets. Easy enough, but I am looking for either a formula or some VBA guidance on how to have the "Standing Equipment" worksheet automatically change it's formulas to incorporate new worksheets as they are either added or deleted, thus avoiding constant formula editing as scaffolds are erected or dismantled.

## Sums- 52 Tables In One Spreadsheet To Represent 52 Weeks

Feb 4, 2009

I have 52 tables in one spreadsheet to represent 52 weeks and have created another table to sum the weekly data but the sum function doesn't do it because of the amount of cells which need adding.

example: c3, c14, c24, c34, c44, c54 etc...