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

Related Forum Messages:
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.

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.

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)

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?

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.

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.

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

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

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

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

ie.

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

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

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.

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

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.

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?

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.

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.

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.

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

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.

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?

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.

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())

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.

Can't Calculate Subtotals
have an excel spreadsheet linked to a network printer, it contains a list of what each user printed, how many pages, and the total cost. It is constantly updated. The total cost column contains the wrong price, so Im using this formula:

=If(C2=0,"",SUM(D2/C2)*(H2))

in EACH field on the total user cost column to extract the CORRECT price. So, I started my macro, highlighted the entire TOTAL COST column, then inserted my formula into each field in that column. The correct price displays for rows that contain data, and rows without data are blank. However, when I try to create a SUBTOTAL (Data --> Subtotals) for each user, I get the following error: "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet."

This is because I applied the formula to the ENTIRE column - even blank cells still contain the formula. How do I get fields without data to be completely blank?

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)

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
Range("i,1").Select
Selection.Copy
Sheet2.Paste
End If
Next
End Sub

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.

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.

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.

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

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

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

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

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

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.

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.

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

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

Conditional Subtotals (according To The Date)
Let's say I have this set of data
A..................B..........C
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?

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.

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.

I have a piece of vba code that uses the date in column D to group by month and subtotal the amounts in column E.

The problem is the subtotal in column E doesnt adjust. So if a user of the spreadsheet adds or deletes rows the subtotals (and grand total) do not up date.

Dim LastRow As Long
Dim NextMonth As String
Dim R As Long
Dim Rng As Range
Dim SubAmount As Currency
Dim ThisMonth As String
Dim TotalAmount As Currency
Dim Wks As Worksheet

Subtotals And Time Calculation
I am trying to calculate the time spent on each task.

I have column A with Task IDs and column B with Time. Column A could have the same task ID repeated but the column B has the time for each entry. There is only start time for each task.

Where the Task is repeated, want to know how many times the task has taken place and overall time spend on one task ID, calculating from the first entry to the last entry.

Adding Subtotals To Get A Total
I have a spreadsheet where I need to take the subtotal from D13, D21 and D 25 and make a total on D26. the same for E & F. I know this has to be very easy but I just can't figure it out.

If Alphanumeric Text Equals Certain Criteria Count Numbers
On my spreadsheet I have multiple rows and cells containing alphanumeric text (text is a combination of a person's name and goals scored) i.e. Holland1 appears in row 3 cell P3 then Holland3 appears in row 6 cell O6.

What I would like to be able to do is have a forumla that looks at every row and cell in my range and sum the numbers of the alphnumeric text if the cell contains a certain name i.e Holland.

I had tried to use the array formula =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(\$1:\$26),1)),0),COUNT(1*MID(A1,ROW(\$1:\$26),1))) which I found on the internet but this only works for one cell and doesn't allow for a set criteria to be met.

Change Cell Color If Date Equals To Friday
is there anyway I can do a conditional formatting of something like that to color a cell if the date in the cell is a friday?

Dates will appear in a column like this:
16-mar
17-mar
20-mar (this is a friday, it should be red)
21-mar
21-mar

File Format Equals Marco-Enabled Workbook
I want to save the Excel file as "Excel Macro-Enabled Workbook." Currently, File Format:=xlNormal within the VBA code and the results saves the Excel file as .xls. How do I write the File Format to save the Excel file as "Excel Macro-Enabled Workbook."

Below is an example:

ActiveWorkbook.SaveAs Filename:=Left\$(ActiveWorkbook.Name, (Len(ActiveWorkbook.Name) - 4)), FileFormat:=xlNormal, CreateBackup:=False

Pivot Table Subtotals Are Wrong!
With the following data Excel's pivottable's subtotal shows underlying MAX total instead of subtotals relating to values displayed.

SalesRepOrderNoItemValue
ACON12334
ACON12322
ACON12412
ACON12424
ACON1248

Pivottable:

Sum of ItemValue
SalesRepOrderNoTotal
ACON12356
AC ON12444
AC Max34

Rather than use SQL (I don't have data on SQL server) or re-organising data by order number, is there a fix using calc items, calc fields or grouping to show correct MAX figures? Why Excel doesn't simply evaluate visible cells seems crazy to me!

Suppress Pivot Table Subtotals
When I have two or more fields on the left of the pivot layout, sometimes I only want the line item data and the grand totals, but not the various subtotals.

Is there a way to suppress the various subtotals created by a pivot table?

Subtotals Error (run Time 1004)
I have inherited a spreadsheet and code that I don't understand much about. I have code

Range("B4").Select
ActiveSheet.PivotTables("Kh07Pivot").PivotFields("Specialty").Subtotals = Array(False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False, False, False, False, False, _
False, False, False, False, False, False, False)
which error (run time 1004) and says 'unable to set the SubTotals property of the PivotField class'. If I make the number of False's equal to 12, then it doesn't error. However I don't know whether it is having an adverse effect on the data?