Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Sum Of Subtotals, If Negative Equals 0

I have a column of numbers that after each sum there will be a subtotal. If the sum is a negative number then the new subtotal will be 0. Attached is a sample.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Sum Cells Where Other Column Equals Given Value
I have two columns, Column A which is a list of numbers and Column B which is a list of different sports. What i want is a formula that adds all the values in column A where the adjacent cell in column B equals a given sport (i will use Golf in this example).

View Replies!   View Related
Compound Sum Or Subtotals
Have a look at the attache example. I have inventory items which have a fixed value. I have several quantity columns to reflect various inventory positions. (OnOrder, WIP, ATP, ATS, OnHand, PAB as at date()...)

At the top of the sheet I need to show sum of quantities and sum of values. In order to compute the values currently I need to hide columns to the extreme right to do the math on a row by row level and then sum the rows and copy the the value to another cell. In this case the cells with yellow background.

Is there a way to be able to write a formula that would return the sum of the qty multiplied by the value without adding an additional column. I would need to function like with filters as well (like =subtotal())

View Replies!   View Related
Find Where The Sum Of Numbers In A Row Equals A Set Number
Is there any way to find where in a row of several columns, the total will equal a specific number or cell?

View Replies!   View Related
SUM Negative Numbers
I'm trying to sum negative numbers using the following formula, however for some reason it's not working in the way that it would if I flipped the < to a > sign.


View Replies!   View Related
Sum Negative Values IF
Using Excel 97, Is there a way to Sum the values in columns if the value in cell B is a negative number. I want the total placed in their respected columns after excel finds 8 Consecutive Blank rows.


B1:B70 (remember the number can change it can also be B1:B80)

B1:B70 is a positive value, but then B85 is negative so begin the calculation from that point.

Sum B85,F85,G85,H85,I85,J85,K85,L85,M85,Q85,R85. And place the value in their corresponding cell after 8 consecutive blank rows appear in row B.

View Replies!   View Related
Sum Only Negative Numbers For A Specific Name.
I understand how to only sum negative numbers but my problem is pulling only certain ones from a list. I have tried numerous IF and SUMIF statments with no solutions. I have attached a worksheet showing what I am trying to do.

View Replies!   View Related
Sum To Ignore Negative Values
I am trying to figure out how to ignore a #VALUE! error in my OR formula. I am using this formula for conditional formatting and I don't want to correct the error because I am using the errored out cells as a fourth conditional format since you can only use three in excel. This is my formlua:


How can I modify this formula so that the whole formula doesn't error out if one of the cells has #VALUE! in it?

View Replies!   View Related
Sum Every 2nd Column If Value Not Negative
I am trying to sum up numbers that are in alternate columns but if the number is negative I do not want that one added. I am getting the standard # name error. Here is my formula: =SUM G3+J3+M3+P3+S3+V3+Y3, If >0

View Replies!   View Related
Status Formula (find Out The Formula Where Numerical Valued Between 1-2000 Equals True And Numerical Under 0 Equals False)
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.

View Replies!   View Related
Sum Positive And Negative Numbers Regardless Of Sign
I have a colum of positive and negative numbers I want to sum all the numbers regardless of the sign. Example

I can not work in another colum or covert the negative numbers in another colum then add them up.

I need last cell to read 16000. What formula do I need?

View Replies!   View Related
Sum Col A If Col B Equals Breakfast
if colum A has a number and colum b say breakfast, lunch, or dunner how do i get colum c to add the numbers in colum A for all the breakfasts

View Replies!   View Related
With Loop + Find Method And Sum The Negative Values
I have a very large worksheet (row count maxed in 2007, and then some), for which I need to do the following: search column A for a string that will occur many times, and then check the 10 cells that follow in its row for negative values, dropping some sort of indicator in the 11th (shading it red or something would be fine). An additional bonus would be if the 10 cells that possibly contain a negative could be summed (the sum could serve as the indicator?). If no negative is found, nothing need be done, and the macro should chug along searching A for the next reference to this string.

My hope was to do a sort of "With Range("A:A"), .Find("MyString")", save position as StartPos, do the 10-cell row checking in a nested IF or For (though the For would take a long while, checking each cell individually), then doing a .FindNext after StartPos until = StartPos (does .FindNext loop back to the top?). The formatting of the indicator cell in the 12th cell in each relevant row doesn't really matter, it's more just for jumping to critical rows.

View Replies!   View Related
Making A Cell Change Background Color Based On If Positive Or Negative Sum Total
I have excel sheet with colored cells.

Positive cash flow cells all have green background,
Negative cash flow cells, all have red background.

I calculate all the "sub- total" columns into one "total sum" cell. Based on this "total sum" I want the cell background to be red if the total sum is negative, or green if total sum is positive. How do I make the cell change to the proper background color based on whether it's a negative or postive total sum?

View Replies!   View Related
Formula To Make Product Of Two Negative Numbers Negative
I have a large dataset (24000 rows) that requires me to multiply two different columns of integers. In some cases, the two integers are both negative and multiplying them results in a product that is positive. I actually need that product to be negative rather than positive. I can't quite seem to figure out the best way to accomplish this.

View Replies!   View Related
Gathering The Sum Of Negative Numbers & Positive Numbers In A Column
I have a column of variances, these contain both negative numbers and positive numbers. I want to gather a sum of all the negative numbers and positive numbers separtely. Basically saying all the positive overeages = this amount And all the negative shortages = this amount. you can see the attached sample.

View Replies!   View Related
Convert Negative Numbers With Negative Sign On Right
I have data that comes from a subsytem that places the negative sign at the right of the number, so it is recognized as text. I can get around this using find and replace and then a second step to multiply that by -1, but is there a formula that can do this for me?

I was trying if(right(A1,1)="-",TBD,A1)

View Replies!   View Related
Positive To Negative If Cell On Left Negative
I have data starting in E7. I want it to go down the column and find the negative numbers. If it finds one then I want it to change the number in the row to the left of it to a negative. So if E67 is a negative number, make D67 a negative and so forth down the line Sounds "simple" but how do I do it?

View Replies!   View Related
Sum Of Numbers Excluding Negative Numbers
How can I get the sum of a range excluding any negative numbers?

I've thought about using a new column and using if statement to separate but it would be easier if i could use the same column.

View Replies!   View Related
Userform Listbox: Check Wether Range Have Negative Values Or Not If Yes Load All Negative Values In The Listbox1 By Clicking Checkbox
I have data in range J2:J365 , H368:H401 & J403:J827. i want to check wether this range have negative values or not if yes load all negative values in the listbox1 by clicking checkbox.

View Replies!   View Related
If, Then Else And Equals.
I could make up a little excel spreedsheet to do the scoring for the little aths. Works out, long time since Ive used Exeal and I have forgotten a lot of it.

1st Problem: If k15 equals "green" and also L15 equals "08" then display "8", if not then display nill. I tried: =IF(K15="GREEN" AND L15="08" ,"1","")

2nd: If C15 equals the same as L15 then display "OK" if not, then display "X". If you need anymore information, I'm happy to provide.

View Replies!   View Related
Equals Last Cell
I have a sheet where there is a column showing all the totals of previous entries, however a couple of the cells shouldn't total but just show the last entry.

The cell is for an ongoing count of year to date applications received.

Can anyone tell me a formula that would look for the last data entered in a row and return that value

View Replies!   View Related
Flag When Cell Equals A Value
Is it possible to run a macro from an excel cell formula?


=IF(A1=1, RUN MACRO, 0)

View Replies!   View Related
Hiding A Row If Value Equals 0
how to hide a row if a value in a cell equals zero?

View Replies!   View Related
Word Equals Charecters In Different Cell
I want to format cell A5 so as to where if I put in one of several keywords into it, cell A7 puts several letters (with spaces)

example A6:Normal = A7:AA AA AA Ive got about 10 select words so just one example would be awsome! 07 excel BTW

View Replies!   View Related
If Cell Equals Then Image File
I am looking for a formula to allow an image file to show when a cell equals a certain text string. If it can be done can it be achieved with multiple images? I am making a database that will calculate from many variables my required glass sizes, given the size and window type and I was hoping to go one step further by making it raise a purchase order from a series of images and other related data.

View Replies!   View Related
Play A Sound When A Cell Equals A Certain Value
Is there any way of playing a sound (or even better, speech) whenever a specific cell equals a certain value or contains a certain string (such as "Alarm")? This has to happen automatically as the cell updates and require no interaction.

View Replies!   View Related
Delete Row If Cell Equals
I am familiar with the macro DeleteRowOnCell, but I want to delete rows when a cell in a selected column has a formulae which is returning empty text. I tried the macro DeleteRowsFastest without success.

View Replies!   View Related
Test If Date Equals A Monday
I want to be able to test if a date entered in a cell (Format dd/mm/yyyy) equals a "Monday". If it doesn't, I want to display a warning message.

View Replies!   View Related
Formula: Cell Value Equals Worksheet Name
Is there a formula I can use to return a worksheet name. I want cell A1 on Sheet1 to = the name of Sheet2

View Replies!   View Related
Automatically Do SubTotals
Client Location Product Cost Sub- total
ABD Here Slurry $125.
ABE There Mud $525. $650.

Where I want to enter the cost and have Excel do the sub-total automatically.
Is there an easy way to do this, remember I am new to all of this? The spread sheet is already 147 entries long and will only grow and I don't want to have to figure out the sub-totals each time.

View Replies!   View Related
Bolding Subtotals
After using the subtotal function, I need to highlight and bold the subtotal rows. There are thousand over rows and it is impossible to do it manually, does anyone has a solution to this?

View Replies!   View Related
Isolate Subtotals
I need the data "pulled down" into the subtotal row, so to get this after I subtotal, I'm sorting by C, and I've got some VBA deleting all rows where COLs A & B are blank (this is the longest part & the part I want changed the most - this gets rid of the non-subtotaled rows), extended replacing "Total" with "" in COL C and then inserting a lookup in A & B to get the data back next to the subtotals.

This takes really long and I'm sure there's a faster way to do this that I haven't thought of. All in all, I'm looking for something that will ONLY keep the subtotal rows, and will fill down the data to them while removing any non-subtotal rows.

View Replies!   View Related
SUMIF With Subtotals
I am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.

I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.

View Replies!   View Related
Countif Cell Equals Active Worsheet
I need to enter a countif formula if a cell equals the name of the active worksheet and also cell A1. My worksheets are not always the same and I also want to be able to copy this formula to multiple worksheets so if the formula states active worksheet then is should work properly when I past it to multiple tabs (at least in my head)

View Replies!   View Related
Copy/Paste If Cell Equals A Word
I am trying to figure out how to go down a list of words and copy/paste a row if it equals a specific ring of words. For example, if want to go down a column and copy the word "Dog" if the word is "Dog" and paste it to the second sheet in a certain column... The thing that I can't figure out is how to step down to the next row until the cell is empty.

Sub Cats_and_Dogs()
For i = 1 To 100
If Range("i, 1") = "Dog" Then
End If
End Sub

View Replies!   View Related
IF Text String Equals Certain Criteria Then Specified Calculation
I have 5 cells with drop down validation lists for the different types of window that we offer. From this list I will select the options and then input some dimensions in cell dimension fields. I then have a cell that gathers the text string from the 5 drop down cells.

My aim is to be able to calculate glass sizes from the text string and the dimensions. Each of the possible text strings will require different calculations and will define the sizes that I need to order. I know the equations that I will need for each of the options but cannot think of how to make them dependable on the text string cell.

I am looking for some way of using IF function to say if the text string returns as certain text then there is a certain calculation to calculate from the dimensions for the glass sizes that I need. I dont know if I've explained it too well nor even if its possible but if it is possible I will find a way as I have done some pretty exceiting things with excel in the past.

View Replies!   View Related
Macro To Inform If A Value In A Dropdown List Equals
I would like to know if I can set a formula or macro to say that if a value in a dropdown list equals, lets say "Business", then show this list of value in a dropdown list in this cell.

View Replies!   View Related
Delete Entire Row If Cell Equals
I have a row range 5:20004. In that row range column R may have 'QLD' in the cell.

I am trying to delete all rows within that range that have QLD in column R or delete the row if R <> 'NSW' (would be a better way just in case there is something other than QLD)

to do this on opening the file automatically.

View Replies!   View Related
Greater Than, If Cell Equals Specific Number.
I have students Kg-5th Gr. I need to identify the ones over-aged when I enter the information. The following is what I'm trying to use but I get Y1 for ever cell. I need it to Identify the student as Y1 for One year overage and Y2 for tow years overage.

View Replies!   View Related
Pivot Table - Subtotals
I have a seemingly very simple question but even though I've worked a lot with pivots I can't find the answer.

clientcode Amount countries
a1 1.000,00 kenia
a2 2.000,00 kenia
b3 1.000,00 kenia
b4 3.000,00 kenia
b5 2.000,00 kenia
c5 1.000,00 senegal
c6 3.500,00 senegal
c7 4.000,00 senegal
c8 5.000,00 senegal

Lets say I have a list like this and I want to count the number of clients (3) or countries(2).

I can only get the total of rows per client but not the subtotal 3 for the number of clients.
a - 2
b 3
c - 4

View Replies!   View Related
Pivot Table - Just Certain Subtotals
I can't find a way to display "just" subtotals which are > 500k ..

View Replies!   View Related
Subtotals Macro Column Name
Is there a macro to subtotal by column name then only total certain columns? Like the data below, can I subtotal by Key then I only want the subtotals to show Price, Sale Price, QTY,, Warranty, Credit, and Cash. So I dont want to subtotal % cash?

key Price Sale Price QTY Warranty Credit % Cash Cash Camera 502 61 10 5 3 15% 15 Phone 1346 234 10 5 3 15% 15 TV 13216 479 10 5 3 15% 15 Camera 484 131 10 5 3 15% 15 Phone 3 231 10 5 3 15% 15 TV 24 12 10 5 3 15% 15 Camera 138 21356 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 12 10 5 3 15% 15 Camera 502 12346 10 5 3 15% 15 Phone 1346 132456 10 5 3 15% 15 TV 13216 61 10 5 3 15% 15 Camera 484 234 10 5 3 15% 15 Phone 3 479 10 5 3 15% 15 TV 24 131 10 5 3 15% 15 Camera 138 231 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 21356 10 5 3 15% 15 Camera 138 12 10 5 3 15% 15 Phone 3168 12 10 5 3 15% 15 TV 1348 12346 10 5 3 15% 15 DVD 1348 132456 10 5 3 15% 15

View Replies!   View Related
Copy Subtotals To New Column
I am struggling to get the totals from a Pivot table using Getpivot data.

could someone please advise how can i get this.

=GETPIVOTDATA("Sum of USD Value",$A$3,"Security Code","Total")

View Replies!   View Related
Conditional Format Subtotals
I have column in which all of the different types of deposits for the day are listed. I also have additional columns listing the types of the deposits along with the subtotal for the specific type i.e cash, credit card etc. I would like the subtotal in one columm to be the same color along with all of the individual deposits in the first column that are included in the deposit.

View Replies!   View Related
Data Table With Subtotals
I have a table of data input, which I would like to be converted to a certain format. Please see the attached file.

The output is derived as follows:

Field2 Sum of amount1 Sum of amount2
Left(Field1,2) under the above Field2 Sum of amount1 Sum of amount2
Field1 (whose left,2 is the same as above) Sum of amount1 Sum of amount2

for each of the Field2 Field1 combination.

So is it kinda like pivot table with each row field having subtotals, except that the row fields are on the same column, and the subtotal is place on top.

View Replies!   View Related
Vlookup For Object With Subtotals
I'm trying to look-up the sub-total of sheet 1, Fund 7000107000(which is an object) from the pink cell of sheet 2.

What formula can I use? ....

View Replies!   View Related
Sorting Results Of Subtotals
how to sort the results of a sheet that I have subtotaled twice by sum?

View Replies!   View Related
Conditional Subtotals (according To The Date)
Let's say I have this set of data
date_______amt_ sub
12/1/05____ 1000
12/1/05____ 2000
12/1/05____ 5000 7000
12/3/05____ 2000
12/3/05____ 9000 11000
12/6/05____ 1000 1000
12/7/05____ 4000
12/7/05____ 2000 6000

So we see a subtotal according to the date, where the total values in chronological order are calculated to be

12/1/05 7000
12/3/05 11000
12/6/05 1000
12/7/05 6000

What sort of formula, then, do I put in column C that subtotals values in B according to the date in A?

View Replies!   View Related
Insert Subtotals Command
it state "Use the subtotals command to sum the totals for each sales person) *Hint: convert the list to a normal range before calculating the subtotals.

I highlight Sales person and click Date but subtotals key is not showing up. i have attach the file its in the Subtotals worksheet.

View Replies!   View Related
Subtotals In Groups Vs Filters
Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.

View Replies!   View Related
Copyright 2005-08, All rights reserved