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


Advertisements:










Totaling Columns


I am tracking sales commissions on a daily basis for each month. In column B are the daily dollar totals. In column C is the % to the quota that has thus far been reached. Column D has the average daily dollar amount needed to reach your quota based on cumulative daily revenue. It is this column that has me stumped.

Once the % to Quota (column C) reaches 100%, I want column D to stop calculating the average daily dollar amount needed.

The quota is $322,786 (C2)

Column B (Daily Totals) = $100,000
Column C (% to Quota) = 31%
Column D (Daily Avg. Needed for quota) = C$2/work days left in month)


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Self Totaling Columns With Names In Each Cell
I have Excel 2007 and have a spreadsheet with 26 columns that I continue to add names to each of the columns. Is there a way that the columns can maintain an ongoing total for each column and update these totals as names are added to each column? Small sample is attached.

View Replies!   View Related
Auto Totaling
I have a column data on a worksheet, the PO number for specific purchases where a PO number may be listed multiple times. One a separate worksheet within the same workbook, I have a PO summary section where PO numbers are manually entered and the next column sums the the total value of each PO.

Is there a way I could have the Summary section identify POs and automatically total them without having them manually entered?

View Replies!   View Related
Totaling Textboxes
I have about 21 textboxes that are going to be watched by 1 textbox called totaltime on a userform.
I created the code below to add up those 21 textboxes, but it's not showing anything even though they are populated, some with zeros and some with one through eight.....

View Replies!   View Related
Totaling Various Cells
Picture a bank statement (several in fact) in Excel format. In addition to all the typical bank statement fields, like date, description, amount, etc., there is also a type field, e.g., food, gas, etc. I am trying to total all amounts of each type, e.g., gas total = $200.00, in one cell automatically. So far, I can't figure out a formula to do this nor a macro.

View Replies!   View Related
Totaling Sub Totals
Let me first explain what my worksheet looks like.

View Replies!   View Related
Totaling Column Information
What I want to see is, is it possible to total up the number of times a specific phrase appears in a column using a formula?

I have a column of information generated from an outside source. The information in each row of the column is either "TRUE" or "FASLE".

Say I have 200 rows, I want to count the total number of times "TRUE" appears in that column out of the 200 rows.

View Replies!   View Related
Totaling With Drop-downs
I have a sheet that has drop-downs with totals affiliated with each option. This is a time recording sheet and I would like for each drop-down type to be totaled accordingly. Any help with an equation would be greatly appreciated.

If you have any suggestions, I can send you the document for review.

View Replies!   View Related
Totaling Years In Cells
In Column B I have various dates i.e [01/02/2008]. I Need a formula that will count the number of times the year 2008 appears in column B.

View Replies!   View Related
Totaling 2 Textboxes In A Form
I have a userform with 3 textboxes. The user will put numbers into 2 of them and the 3rd will add the other 2 textboxes together. My problem is that the result is just stringing the values together and not adding them together. So if textbox1 is 2 and textbox2 is 4 the result is 24 and not 6. Can someone tell me where im going wrong?

View Replies!   View Related
Using Lookup, Then Totaling The Row
I'm trying to use Lookup to find a particular name, then when finding that name I need to total what's in that row (numbers). The Lookup is successful, but I can't get any totals out of it. Tried using Offset to begin summing once I got to the row I was Looking for but that wasn't doing it (or doing it wrong)

View Replies!   View Related
Sum Totaling Of A Single Cell?
Is there any way that one would be able to sum total a single cell?

I have my numbers listed in column A & would like the return in column B .....

View Replies!   View Related
Totaling, Summing & Formating
I have a spread sheet that I am trying to format the data from. It contains five columns, 2 of which are important for this formating.

-Column A is Account name, and there can be a varying number of accounts with the same name, as well as a varying number of different account names (they are sorted so all similar accounts are together)

-Column E is an indicator/used for a calculation, either 0 or 1.

I need to do two things ....

View Replies!   View Related
Totaling A Column On Each Printed Page
Is there a way I can display the total of a column on each printed page?

I have a table which is about 6 columns long but is hundreds of rows down. I would like the total of one of these colums to be displayed on every printed page. Is this possible?

View Replies!   View Related
Totaling Numeric Amounts Based On Text In Another Column
I would like to total numeric amounts based text in another column.

For example, I have a bunch of rows in "column F" with the same text (company name) and in "column I" i have numerics. Is there a way to total "column I" based on the same text of "column F"?

View Replies!   View Related
Totaling "if" Statements
I have a column of "if" statements that output in a numerical value (2 for instance). I need to total this column of outputs. I ensured the column was indeed formatted as a number, but it still displayed as a letter (left justification) and will not "sum". I am told I cannot total formula columns, so we tried copying the column and "Paste Special" "Values" into an adjacent column ....

View Replies!   View Related
Totaling Column B If Column A Is Equal To "Text"
I was using the formula below and it returns a count of how many times a salesman is quoted. Now I am looking something very similar. This time though I am looking to total the price of the second column instead of just counting how many times there is something in the column.

SUMPRODUCT(--(Active!$D$4:$D$200=$A35),--(TEXT(Active!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Cold!$D$4:$D$200=$A35),--(TEXT(Cold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Wins!$D$4:$D$200=$A35),--(TEXT(Wins!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Sold!$D$4:$D$200=$A35),--(TEXT(Sold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Lost!$D$4:$D$200=$A35),--(TEXT(Lost!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Dead!$D$4:$D$200=$A35),--(TEXT(Dead!$C$4:$C$200,"mmm")=E$32))

View Replies!   View Related
Counting Formula (compare Values In 2 Separate Columns To See How Many Times The Same Value Appears In Both Columns)
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.

ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678

In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.

View Replies!   View Related
Copy Columns Between Worksheets If The Columns Don't Match
I have 2 worksheets in which I have to copy one column of cells from one to
the other. The problem is that the "main" worksheet lists nearly 3,500
clients and I have to copy numbers into the "main" worksheet on weekly basis
from another worksheet that lists only about 1,100 of those 3,500 clients.

Both client lists are in alphabetical order, but I obviously cannot just copy
one column into the other, because then the numbers won't be matching the
correct clients.

View Replies!   View Related
Subtracting 2 Columns Of Data From Each Other When Labels In Separate Columns Match
I have four columns of data, as follows:

label 1, value 1, label 2, value 2

I need to create a formula in the fith column that for each line will tell excel to:

look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.

I have tried doing this with SUMIF but am getting nowhere fast....

View Replies!   View Related
Copy Columns Resulted From Another Columns Operations And Paste In New Sheet
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.

View Replies!   View Related
Turning 64 Rows And 18 Columns Into One Row With 1152 Columns
I have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).

In other words, my individual data files look something like this:

Participant 1 (title of data file)

Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56

and I want to compile all the data into a single file that looks like this:
Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ...
1 55 43 65 54 43
2 . . .

I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.

View Replies!   View Related
Enter And Highlight Data In Columns Based On Other Columns
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column

"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................

View Replies!   View Related
Text To Columns/time Formatting Split Them Into Columns
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

View Replies!   View Related
Copy Multiple Columns Groups To 1 Group Of X Columns
Example:................

The key point is that I have over 400 columns grouped in three, offset by one row down for each (this is due to a different formula I have working). Each column is 60 numbers long (not including empty cells above data). I want to copy all of these columns into a single set of three columns on another sheet (Sheet2). (These will be the fifth, sixth, and seventh columns, E, F, and G. If it would be simpler, I could simply copy the third of each set of columns on Sheet1 (C, F, I) to column G of Sheet2. Then there would be a total of 144 columns being copied (60 numbers per column).) All of this copying has to be values-only as the original columns consist of formulas referencing outside cells. Sheet2 should look like this:.............

View Replies!   View Related
Move Data From 2 Columns Into Multiple Columns At Each Change
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............

View Replies!   View Related
Concatenate 2 Columns & Delete Orginal Columns
I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.

Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................

View Replies!   View Related
Sort Multiple Columns, With Blanks, And Move All Columns Into One
I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:

Sub CopyToA()
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
ActiveCell.Offset(0, 1).Select
Loop
End Sub

However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.

View Replies!   View Related
Skip X Columns In Formula Reference When Dragging Across Columns
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.

View Replies!   View Related
Lookup Date In 4 Columns And Get Reults In One Columns
i'd like when i lookup abt specific in range (4 Columns is my lookup range)
get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....

View Replies!   View Related
How Do You Sort Columns So That They Match Up With Other Columns With Like Data
I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.

View Replies!   View Related
Match 2 Columns To 2 Seperate Worksheet Columns
Now i have one excel sheet with two separet sheets in it ( Sheet 1 ) and ( Sheet 2 ) . I have in sheet 1 a column A with material codes and ColB is discreption and Col C IS Prices . But in Sheet 2 Col D is materila Code and Col I is a price .

the recordes in sheet 1 are around 11000 but in sheet 2 are around 2200 where the sheet 2 has a specific materials from sheet 1 .

Now i want to update the prices in sheet 2 from sheet 1 for each item after confirm that the materila code in sheet 2 equal in sheet 1 so copy the price from sheet 1 to sheet 2

Sheet 2 is old prices and sheet 1 has new prices so i need update the new prices in sheet 2

View Replies!   View Related
Insert Columns Between Multiple Existing Columns
I have a spreadsheet with thousands of columns. Due to poor planning on my part I need to insert a column between each existing column (e.g. I need to insert a column between the existing columns A and B, B and C, etc...). I would rather not have to do this manually thousands of times.

View Replies!   View Related
Columns & Stacked Columns Chart
I am trying to get an Excel chart to include columns AND stacked columns in the same chart with no success. I have attached the Excel chart where I could only get stacked columns and I had to use a line chart for the secondary axis. I could not figure out how to get the secondary axis to be a set as a column just like the primary axis. I thought this could be done in Excel, I have searched the forums and tried to play around with the gap width and/or include a blank column but I still haven't been able to get this to work.

View Replies!   View Related
Match 2 Columns To Copy Information In Other Columns
I have 2 workbooks (workbook1 & workbook2). Information in column A and column N must match both workbooks per row/ cell (ie. if A2 and N2 in workbook1 match workbook2, copy information from 3 other columns (S, T, U from from workbook1 into workbook2 - cells S2, T2, U2). Workbook2 may have information in column S, T, U, but I need to over-write it with the information from workbook1. If the information does not match per that row/cell (ie. A2 in both worksheets are the same, but N2, they are not, so go to the next line that does match and copy the information into worksheet2). I do not want to use a V- lookup for this problem. I really need a marco.

View Replies!   View Related
Rows To Columns Or Columns To Row
I wrote a short VBA to change known data from rows to column and columns to row.

Data is like this:

Hello Hello Hello
Hello Hello Hello

I can chang it to Hello .... Hello
or
Hello..
.
.
.Hello
I need to figure out a way to do this when data is not fixed - ie:

Hello Hello Hello Hello Hello
Hello Hello Hello
Hello Hello Hello
Hello

or

Hello Hello Hello Hello Hello Hello
Hello Hello Hello
Hello Hello Hello

Can you offer suggestions on what commands I can use to find the begining and ending of data, so that no matter what format it is in I can change it to one row or one column?


View Replies!   View Related
Lookup 2 Columns And Match To 2 Other Columns
I am trying to do a (what I thought was a) simple lookup involving 2 columns.
Pls see attached example. I am trying to find the "?" values, by matching column A&B of the input to the Data columns and returning the $$$ column.

I thought this wouldve been a piece of cake but its got me stumped!


View Replies!   View Related
Re-ordering Some Columns (groups Of Columns)
I must put in descendent order some columns, but the thing is a bit more complicated...
As my english is not very good i am gonna explain it with a simple example:

----------------------------------
1 4 2 5 rabit fox dog cat
2 6 1 3 bird fis wolf mouse
----------------------------------------

Ok, i must order the numbers in a ascendent way, but reordering one number means also reordering its correspondient name (the name of the animals at its right).
So, again, with an example you will see it better:

Ordering the first row it must appear like this:

----------------------------------------------
1 2 4 5 rabit dog fox cat
----------------------------------------------

I didnt know if i had to post it here or in other subforum and either if it can be done with excel.

View Replies!   View Related
Join Text Of 2 Columns Into 2 More Columns
how to add two columns of single words together, so that all possible word combinations are seen. For example:

Column1:

Horse
Pig
Dog
Sheep

Column2:

Run
Walk
Sit
Roll

So... I'd like Column3 to look like this:..........

The issue is I have about 100 words all together so there will be a lot of results! Is there a way I can enter a formula to do this?

View Replies!   View Related
Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.

N.B. The headings of Columns C & D are :-

C = Value Found in Column A

D = Value Found in Column B

Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)

View Replies!   View Related
Match Columns Of One Table To Another Two Columns Of Another Table
I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.

One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.


View Replies!   View Related
Get Value From 1 Of 2 Columns
Get a possible value from 1 of 2 columns otherwise return 0.
Here's my formula so far, but I'm having meltdown


=If(ISNUMBER(H14,H14,If(ISERROR(MID(H14,1, FIND(" ",H14))),If(ISNUMBER(I14),I14,
Sample data attached


View Replies!   View Related
Using Only Columns
I want to find an average across a range of columns from D5 to AJ5, but using only every other column, i.e. D5, f5, h5, etc. Normally I would have =average(d5:aj5), but how do I tell excel to ignore every other column?

View Replies!   View Related
Copying Several Columns
Hello. I download several spreadsheets monthly that have a large number of columns (A to EZ). In every excel spreadsheet I need to copy only 18 different columns of data from the sheet which are the same columns each month, and put them into a new spreadsheet. It is very time consuming to highlight and copy a few columns at a time throughout the whole sheet.

I am not very experienced in excel, but is there a simple way to copy all of these columns at once out of the spreadsheet and paste them at once into a new one? I would think there must be some way to enter the column letters I need to pull them out.

View Replies!   View Related
Need To Create 2 Columns From One
I have a spreadsheet in which I want to extract or create two different columsn one will have the name of the store and the other will have the SKU item number. the way it is right both are on same column. I have attached a sample.

View Replies!   View Related
NEED ASSISTANCE FOR COLUMNS
Is it possible if 0 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet A2 to A10 in work sheet Random Picks. And fill column D7 to D15 In sheet team matches with the contents of them cells.

If 1 cell is filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet B2 to B9 in work sheet Random Picks. And fill column D7 to D14 In sheet team matches with the contents of them cells.

If 2 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet C2 to C8 in work sheet Random Picks. And fill column D7 to D13 In sheet team matches with the contents of them cells.

If 3 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet D2 to D7 in work sheet Random Picks. And fill column D7 to D12 In sheet team matches with the contents of them cells.

If 4 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet E2 to E6 in work sheet Random Picks. And fill column D7 to D11 In sheet team matches with the contents of them cells.

If 5 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet F2 to F5 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.

If 6 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet G2 to G4 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.

View Replies!   View Related
Certain Columns To Rows
I have 20,000 columns of data that I need turned into 4 rows.

I know about copying and paste special (transpose), however, the data is in groups of 4 columns

Example:

1. Name
2. Street
3. City, State
4. Number
5. Name
6. Street
7. City, State
8. Number

and I need it to look like
Name Street City, State Number

So is there anyway that I can make it turn columns into rows by groups of 4?


View Replies!   View Related
Duplicates In Two Columns
I have a list of numbers in column A of my Worksheet, and a list of names in column B.
There are duplicates in both columns:

A B
234 John Smith
253 Charles White
461 Mary Carlsson
876 Erica Alvin
954 Joe Brown
234 John Smith
461 Mary Carlsson

The duplicates in column A and in column B are correct, because the same names correspond to the same numbers. I would like a formula or VBA macro, if possible, to detect when there is no correspondence, for example, if the last row above were

A B
461 Ben Wayne - where it should be Mary Carlsson.

Is it possible to check all the rows of both columns and identify errors?


View Replies!   View Related
Sum The Columns
I am working on a project that I have the following information: ......

View Replies!   View Related
Rank For Two Columns
I have two columns of data - weights recorded during an angling competition. I currently have a rank set (in C) for the first column (A - pounds) to give a position for the highest weight recorded during the competition, the second column (B) records the ounces.

I need to be able to include the second column in the rank to cover the eventuality of two people recording the same number of pounds but different ounces.

View Replies!   View Related
Non Printable Columns
How do I tell Excel that I do not want columns E, G & I printed, but I still want them visible on the worksheet, so that I may work on them


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved