Formula For Summarizing Various Columns?
Feb 18, 2014
I would usually do this in a pivot table (which works fine) but I need something else as the person I am passing this on to will not know how to change the range and this 1 report is a part of a larger report which should just be as simple as: Data download >>> calculations run automatically >>> print
I need to sum up various bits of information. The attached file is the example. The DATA tab holds the information and the REPORT tab shows the column headers that I am after. These columns are as below:Contractor Name: The formula should look through the DATA tab and bring back a contractor nameApproved Quotes: Then taking the name from the above field, the formula should add up column T where column AP = YesApproved WO Count: This one should just add up how many rows the above total is made of.Rejected Quote: Then taking the name from the contractor field, the formula should count add up column T where column AP = NoRejected WO Count: This one should just add up how many rows the above total is made of.
The PIVOT EXAMPLE tab holds the example with the red highlight being excluded.
View 9 Replies
ADVERTISEMENT
Oct 28, 2009
I was asked at work to create a report that takes raw data from a questionnaire and compiles it in an easy-to-read report. (Little do they know that this is how I'm such a wiz at excel ;-) )
It seems pretty simple, but I can't seem to figure it out! On the attached workbook, the first spreadsheet,"SUMMARY", is the jist of what the report should look like - the average score for each question by floor. On the next spreadsheet, "RAW DATA", is a sample of what the raw data looks like - just a list of each room number and the score it received by question. the first 2 digits of the room number signify the floor (ex. 201=2nd floor, 1215=12th floor, etc.).
My major issue was with getting the score for each floor without having to manipulate the raw data - this report will eventually become a shell that can be used with new data every month. Is there a way to make a range equal a number (ie. 200-299=2)?
View 3 Replies
View Related
Jul 10, 2012
I have many sheets in a workbook (Sheet1, Sheet 2 etc), and I am trying to create a final summary sheet (Summary).
In cell A1 of the summary sheet, I want the information from Sheet1-A1 to be transposed (so =Sheet1!$A$1).
In cell A2 of the summary sheet, I want the information from Sheet2-A1 to be transposed (so =Sheet2!$A$1).
I've tried to drag/drop the formula on the summary sheet, but I can't seem to make it change the worksheet and not the cell.
I don't want to have to manually input the formula for each cell.
View 1 Replies
View Related
Aug 7, 2007
I have a basic bookkeeping workbook with multiple sheets each representing a differenct account. In each sheet, an entry is entered on a row with main columns being date, credit (where a positive amount goes), debit (negative amount goes), and then a series of columns representing the type of credit or debit where a simple "x" is placed.
This style has worked well in the past becuase we merely just sort by column and the "x"s clump all credit or debit types together and we just sum them at years end.
However, we are now trying to have the numbers report to a main sheet that will summarize them monthly; basically where the first column is all the different types of credits and debits and the next 12 columns are the months.
We know how to refference the other sheet to get the data, but where it gets complicated is summarizing ALL the sheets together since they all have varying number of entires per month.
Is there some sort of way I can write a formula (or series of formualas) that will allow it to collect all the data from one sheet by only looking at the month column, and add that up, and then do the same for each sheet?
View 9 Replies
View Related
Jul 12, 2009
First sheet is comprised of 12 grouped cells. Each group consist of 7 rows and 6 columns
the above mentioned groups are bwoke down with 6 lines of data and 7th row is totals of the 6 boxes.
Each gtoup will have totals, even if they run over to next group
The first column is a task number, which will be the main component. A task number can be used multiple times and can run over to next group of cells
Here is an example of above mentioned sheet
task# Start time end time Description
999.......15:00.......15:05.........Open Mail
999........16:05.......16:55........sort claims
A new task number will move to the next group of cells
748........15:05.......16:05.........23 inches
2nd sheet will be a time tracking sheet. Here is the info from this sheet
999......15:00.....15:05.....open Mail
748.......15:05.....16:05......23 inches
999........16:05.....16:55......sort claims
The 2 sheets look alike but on first mentioned sheet info is broken down by task number
View 10 Replies
View Related
Feb 26, 2009
I'm stumped on what I know is a pretty basic problem. Maybe i'm just trying to over think it.
I have a table of sales data...One field is the date it was sold, one field is the amount it sold for. The date field isn't in order and it contains dates over the past 12 months. I need a way to total the amount of sales in each month and not through a pivot table. I am able to count how many entries there are, but I can't find a easy way to do a count of how much was sold in each month.
View 6 Replies
View Related
Oct 3, 2008
In the first sheet (EQ MOVES) I have:
Column A contains a list of some 300 different pieces of equipment.
Column D is where the foremen enter the job number (4 digits) and job name of where the foreman wants the equipment to go the next day.
In the second sheet (PERSONNEL MOVES) I have:
Column A contains all employee names.
Column D is where the foremen enter the job number and job name of where the foremen want the employees to go the next day.
In the third sheet (SUMMARY) I have:
A box in A1 where I type in a job number (4 digits) but no job name.
A2 says "EQUIPMENT and B2 says "PERSONNEL".
A3 and below should list all equipment to go to that job listed in A1.
B3 and below should list all personnel to go to that job number.
View 9 Replies
View Related
Nov 23, 2008
I have a master worksheet and 4 scenarios. The master worksheet has all the details and I need to extract each scenario into separate worksheets. I can "if" a statement to pull only those rows that have "x" in a scenario but how can i delete all the rows that do not have a corresponding "x" for that scenario?
In essence, how do i extract only the info for a particular scenario without having to go back and delete the empty rows?
View 9 Replies
View Related
Oct 4, 2009
in range B9:B187 I have names and I have their attendance in I9:AM187
I have all the dates in I8:AM8
attendance codes are PL SL CL LWP TRG & P. I want to segregate all PL SL CL LWP and TRG
I want the output in a different sheet where in column A I will should get name, in column B I should get type "SL PL CL LWP TRG", in column C I should get start date and in column I should get end date.
View 10 Replies
View Related
Jul 8, 2009
Sheet1 contains a large set of data, including a date and a corresponding value.
Sheet2 (Summary) has a column called "Begin Date" and a column called "End Date". How can I use a formula to sum every piece of data that fits within the two dates?
View 5 Replies
View Related
Dec 6, 2009
I have a Petty Cash Workbook that consists of a Cash Receipts worksheet, and two separate credit card worksheets. I would like to be able to summarize the data from these three worksheets to a fourth worksheet called “Yearly Totals”. I’ve copied and modified code from Ron deBruin’s website but I’m not getting anywhere with it. I’m constantly running a Runtime ERROR 9 with this line:
View 2 Replies
View Related
Dec 26, 2012
I have a spreadsheet with 12 tabs, 1 for each month in the year. On each tab I have employee names (column A), followed by the premium they pay for their health insurance (column B). I'm attemting to create a list of each employee and how much they paid for helath insurance over the year. Since employees come and leave thorughout the year, the lists aren't the same on each tab. However, I was able to create a master list of all possible employees and placed this list into a new tab.
I'm vaguely farmiliar with the SUMIF function and I believe that this would be the best approach, or some possibly sort of vlookup.
View 2 Replies
View Related
Apr 26, 2013
I wold like to summarize values of a certain numbers of enties (eg. 5), on a specific criteria, but discarging earlier entries of the same criteria. An example being:
Entry
Value
Entry A
1
[Code]....
If I would like to summarize the values from Entry A, but firstly do so from number three entry buttom-up, and only the prior five values, how could this be done?
View 9 Replies
View Related
Aug 5, 2014
I created a large table with data (1700 lines long) and am looking to create a sheet that will use it to do a call for totals whilst filtering for a specific line.
This is what I have currently, filtered by delivery date and key ("furniture desc").
The 58 and 54 are totals for that particular product which I would like to ideally see on a separate sheet.
result1.JPG
result 1a.JPG
What I would like to achieve is this: result2.JPG
What kind of formula would I need to use to first filter by date, then by key, then to tally up the quantities and drop them on another sheet?
Bearing in mind that other keys are intertwined on the Y axis so I can't use =SUM(xx:xx)
View 2 Replies
View Related
Feb 9, 2012
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
View 9 Replies
View Related
Apr 21, 2012
Here is the attached Excel file and the following is the desired output of the macro:
1.) List the data (Names) of the Columns D (Input), F (Analyze), and H (Output) in Sheet1 to Column A (Name of Person) in Sheet2. There should be no repetition of two names.
2.) Count the number of entries of each person in the Column D (Input) in Sheet1 appears per month (basis is the Input Date column E) and record into the corresponding month in Sheet2 under the Input Header.
3.) Add the total of the 12 months in the YTD column under the Input Header.
4.) Repeat steps #2-3 for the Column F (Analyze) and Column H (Output) of Sheet1 with the results recorded in their corresponding headers in Sheet2.
5.) Note: The data in Sheet1 is a running data and continually adds up as the current year goes by. If there is a way the macro could take that into account it would be much better.
HERE IS THE LINK OF SAMPLE FILE: [URL]
View 1 Replies
View Related
May 19, 2007
I've got a tab with 32K+ lines of data in six columns. I need to be able to summarize it in a table based on two criteria (e.g. Region, Line of Business). I am currently using 16 sumproduct formulas (=sumproduct((type=a5)*(reg=a4)*revenue)) where type is the line of business and reg is the region column, and 8 vlookups per region. Each tab will have about 8 regions and I'll have 4 tabs. As you can tell, this spreadsheet will quickly get out of hand in terms of calculating speed. I can't really use a pivot table because of: 1) formatting constraints 2) the vlookups refer to a separate tab than the main data dump. What alternatives do I have other than array formulas to get the data in a nice little summary form? The formulas/presentation aren't very complicated, but I have yet to come up with a good alternative to the above.
View 3 Replies
View Related
Oct 27, 2013
My company would receive RFQs from potential customers inquiring the availability of different products, and we are trying to summarise these requests to find a trend.
Each product has several attributes, such as descriptions, keywords, manufacturer's code, etc. The RFQs are all different, with each request providing some attributes while missing others. The only reliably uniform attribute is the product number.
I am trying to build a database with the maximum amount of details on each requested product, therefore consolidating the different requests. I already put the source data on the same sheet and sorted the product numbers. I also know how many instances there are for each product number. In other words, my data look like this:
Product number (sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
[Code] .........
Note:
1. The product numbers and repeat count are reliable
2. All the attributes are valid, i.e. as long as one can fill a blank I would take it
3. If none of the instances provide a certain attribute, it is acceptable for it to be left blank
And I am trying to turn it into this:
Product number
(sorted)
Repeat count
Attribute 1
Attribute 2
111a
2
aaaa
bbbb
123456
1
abcde
100x
3
123456
wxyz
I already spent a whole day trying to do it with MATCH, OFFSET, VLOOKUP etc to no avail. It seems I would need some kind of VBS with loop and array functionalities that are beyond me.
View 3 Replies
View Related
Oct 13, 2008
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 4 Replies
View Related
Mar 26, 2014
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
View 1 Replies
View Related
Nov 15, 2009
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 3 Replies
View Related
Mar 31, 2014
See attached spreadsheet. It's an imaginery list of orders. Raw data on the left, summary on the right.
F is a reduced list of A, G is how many times each appears in A. H is supposed to be the total values of each order, ie quantity in B * value in C.
How do I calculate H?
View 2 Replies
View Related
Apr 26, 2014
i am trying to complete a spreadsheet which most of it is done but one column i have i have to average everyother square in each column which i did but when i total those columns at the end i get the dreaded #div/01 which means any value that i dont have in a particular column it will not add is there anyway to fix this. i am attaching a sample of the spreadsheet so you can see exactly what i am trying to do .
View 6 Replies
View Related
Apr 9, 2009
I have some student marks in columns B to F. If some one get below 40 marks he fails and the subject in which he has been failed will come in Remarks column. If he has passed in all subjects remarks column will show Pass.
To bring failed subject initial in Remarks i have to create 5 additional column and then thru Concatenate i joined them.
Is There any formula to show fail subject in Remarks column without creating 5 additional columns?
View 14 Replies
View Related
Jun 24, 2008
I am currently working on a data analysis project (data mining) and need to collect and later analyze statistics for the inputs which control a series of calculations. These statistics are shown in the Statistics 1, Statistics 2 and Statistics 3 cells in the workbook that I attached. The inputs are X,Y; all possible values for these inputs are listed in the N,O columns. Basically I need a macro which would take the values from these two columns and place them pair after pair into the controlling cells (K3, L3), then it would copy cells H2 through L3 (updated stats) to a new sheet after each copy operation - so that I will finally have a list of statistics for all of the input pairs.
View 3 Replies
View Related
Jun 9, 2014
i would like to add to the basic if statement in my attached column M to say if the result of this if statement is 0 then give me Q.
The statement i currently have is =if(p2=0,n2,p2)
I am sure this is simple but i have been staring at this formula for so long today i can't see it.
View 3 Replies
View Related
Jun 29, 2007
I have a spreadsheet with a unique key for a customer in column A, and then I have the data by month in columns B-D. Some customers have multiple lines, so I have used a sumif to sum for that customer. My question is this: I would like to know if there is a way to write a formula such that the value returned is the sum of all three columns (B-D) so I would see the total for a customer for the quarter. I can get around this by writing three sumif formulas, but I figure there must be an easier, simpler, way.
View 9 Replies
View Related
Apr 4, 2014
I'm trying to create a formula for text to columns if a SKU is put into a box.
Ex) I put code 5495307H7G-**--A into cell A1. I need to split it after specific positions, so it breaks into nine individual codes (9 cells) in the adjacent boxes.
549 53 07 H 7 G- ** -- A
I've seen formulas for searching for spaces and splitting, but is there a way to split one long code at specific points?
View 5 Replies
View Related
Jan 25, 2007
I have two columns One contains a date the other contains $
I want to add the $ column if the Date column contains a date greater than 12-1-06. If the date is not greater than 12-1-06 I don't want it to count.
View 14 Replies
View Related
Aug 28, 2009
I need column F to be matched to column E, i want to see how many user id's from column F are in column E and for them to be highlighted in column E. note that the user id's might reflect more than once in column E. See attached..
View 5 Replies
View Related
