How To Create Sums Of Columns Based On Date And Category Criteria
Nov 1, 2011
I have spent a good amount of time trying to create the below using botched IF functions, SUMIFS, SUMIF formulas etc.
Basically I have a sheet (called Sheet1) with the following columns:
A - Date (by individual day DD/MM/YYYY)
B - A catagory (so using the old example, Apples, Orange, Pears)
C - Price.
I would like to be able to create a formula, whereby I can sum the Total sales for each catagory across each month onto a summary sheet (sheet2).
I would use pivot tables, however am not sure how i can make all the require data appear permanently in the best way, especially if there is a one liner that can be done more easily. So far I have managed to create a sum dependent on month, but have been unable to encorporate the catagory.
View 1 Replies
ADVERTISEMENT
Jan 23, 2012
I have a workbook with many, many worksheets. On each worksheet there is a column called "Cost Code". I need a Macro to locate all like "Cost Code" values and extract data from several cells along that row. Macro would then tally the values from all those like cells on numerous worksheets and place sum on a seperate worksheet in the same workbook, on a "Totals" worksheet page.
For instance -The "Cost Code" value Macro to look for is 1000. This value is located on several worksheets. For every instance of "Cost Code" 1000 Macro is to find value of "Material Costs" in that row (always column M) and value of "Labor Hours" in that row (always column P). Macro would tally all "Material Costs" for "Cost Code" 1000 and put that sum on a "Totals" worksheet page in a specified cell. Macro would do the same for all "Labor Hours" for "Cost Code" 1000.
View 3 Replies
View Related
Apr 27, 2014
I need new workbooks based on category name in the below list. Respective sheets should be copied from the existing workbook with items as sheet names. List can be updatable.
category(workbookname)Items(sheets)
Vegetables carrot
vegetables brinjal
meat chicken
meat Fish
meat Mutton
grains Wheat
grains Rice
View 4 Replies
View Related
Dec 24, 2009
Attached is an example of what I am trying to do.
I am looking to transfer the sums for a calendar month in one column to cells in another location for each month.
I put some notes in the sample attached, I appreicate any help getting this to work.
View 10 Replies
View Related
Dec 31, 2013
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
View 3 Replies
View Related
Aug 6, 2007
I am looking to create a formula which sums the values associated with several defined names. For example, I have a workbook with the following defined names SalesPerson1Total, SalesPerson2Total, etc. and these amounts are all sourced from multiple tabs. The amount of defined names (i.e. 'SalesPersons') is variable, therefore, I want the formula to read Sum the values of all defined names which are named with the following convention 'SalesPerson(X)Total'.
View 9 Replies
View Related
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!
View 9 Replies
View Related
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
View 7 Replies
View Related
Oct 27, 2009
..............
View 12 Replies
View Related
Jan 12, 2014
I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
View 6 Replies
View Related
Jan 29, 2014
I have 2 worksheets with data. One of them is a bench mark template or expected times to complete devices based on batch size and product. The other worksheet is a report generated that shows the actual time techs worked on batches of equipment by product type. I am trying to automate a report that I will need to run every week. I have most of it automated but need some calculations. I want to calculated the expected time to complete the work, based on the data provided in the benchmark worksheet.
I do not want to use an average, and I want to use the expected time of completion based on batch size and product relative to the tech report. Column T is highlighted and where I want this formula to be entered. So if the Total Batch items is 1, then the expected calculation uses that time relative to the product and then multiplies that number to the batch size. Or if the batch size is 3, then it uses the time relative to a batch size of 3 and then multiplies that number by the batch size on the tech report and is entered in the highlighted column.
I have attached the workbook I am working with : Production Report.xlsx
View 9 Replies
View Related
Feb 28, 2013
I have over 1000 of rows of data and I want a formula or a Macro to create 5 new records (rows) based on the following:
Below is how data in master sheet1 looks like...
STORE#ZIPCODEID #1ID #2ID #3ID #4ID #5
10134567A100B200C300D400E500
10245678A200B300C400D500E600
...
...
With the formula or a macro I want it to look like this in Sheet2 in the same file...
STORE#ZIPCODEID #
10134567A100
10134567B200
10134567C300
10134567D400
10134567E500
10245678A200
10245678B300
10245678C400
10245678D500
10245678E600
. . .
. . .
. . .
As you can see there are 5 records per STORE# listed in Master Sheet1.
View 3 Replies
View Related
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.
View 3 Replies
View Related
Sep 21, 2012
Col A has list of order numbers, col D has list of corrisponding order status (open , closed etc etc)
I simply want a formula to display a list of all the 'Open' order numbers in a separate sheet.
Obviously to size of the list will vary depending on how many open orders there are.
View 1 Replies
View Related
Apr 13, 2014
I am trying to formulate a commission form which looks up the name of the sales person and then displays a list of the deal names withing Q1.
View 1 Replies
View Related
Aug 31, 2007
Our reservation system exports a spreasheet daily which contains hundreds of records relating to bookings. The worksheet has an ID column which is not unique, but is sorted alphabetically. I need to write some code to delete all records below the first 'set' of records relating to the first ID value found. i.e. i want to end up with only the set of records relating to the first ID found. So the basic logic would be to look at the value of the field in the ID column of the first record and then iterate down the rows until it came to a different value and then delete all rows below that. Its simple in concept, but im stuck on how to write this.
View 9 Replies
View Related
Sep 18, 2013
Basically, I have a table of data in Excel 2010, akin to the demonstration below (just much, much, MUCH bigger).
Category
Genre
Title
Author
Year
[Code]....
View 2 Replies
View Related
Dec 23, 2008
I need a formula to count list items if the date is between a specified parameter. I am trying to count how many help tickets were logged for a particular group or bureau during a specific week.
How many AA's between 12/1/2008 and 12/8/2008?
Bureau Date Opened
AA12/1/2008
AA12/1/2008
AC12/2/2008
AA12/6/2008
AB12/12/2008
AE12/15/2008
AC12/16/2008
AD12/17/2008.........................
I know how to count a list of items when you identify what you want it to count (I11 (Access)) and where (Application (Named Range)) but I can't figure out how to include the date parameter.
I J
Access23 =COUNTIF(Application,I11)
Acrobat2
ADMIN4
View 4 Replies
View Related
Feb 25, 2014
I have a forecast monthly trial balance sheet and an Income Statement Analysis sheet. I am analyzing the Year to Date performance for Dec-2013. I need a formula that will match the income statement line i.e revenues, accounting expenses etc. and then sum horizontally from Jan-2013 to Dec 2013 ( if YTD month' Dec-2013' is greater than or equal to date range then sum horizontally the corresponding income statement lines up to the reporting month).
View 2 Replies
View Related
Jan 15, 2014
I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
V
W
X
Y
Z
AA
AB
AC
...
FB
[code]....
List of Dates Absent
x
x
x
WR
x
x
x
1/23, 1/24
[code]....
View 2 Replies
View Related
Jul 21, 2014
I'm having serious difficulties with getting together a formula for a little bit complicated lookup.
I have 2 tables. In the first table I have categories in the first column (e.g. red, blue, green), in the second column I have dates (DD.MM.YYYY). And in the third column I would like to get the values from the second table.
In the second table I also have categories in the first column, dates in the second column und values in the third.
I would now like to get the values from the second table that coincide with the categories. And in addition the dates should also be equal to the date in the first table OR the date from the second table should be as close as possible but BEFORE the date in the first table.
Example
Table1
Red 07.08.2005 ???
Table 2
Red 18.04.2005 three
Blue 11.06.2005 one
Red 06.08.2005 four
In this example the value that should be looked up is "four".
View 12 Replies
View Related
Jun 15, 2007
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.
Have attached a sample xls..
View 5 Replies
View Related
May 14, 2013
I am working on a pivot table just like the one on the picture here [URL] .......
Ideally I wanted the sums by date to be displayed horizontally and not vertically as shown in the picture, but was unable to do so. Anyways, I want to be able to filter those dates, so I can display data just from an specific date, and then change this date whenever I want and the new data will show up.
I tried doing by the checkbox that appears under the "data" dropdown on top of the column; However, when I uncheck a specific date, it dissapears and I have to add it again if I want to see it. Is there a way to keep the values on the dropdown even after I uncheck them? Or even another way to filter the information? Below is the pic of the dropdown I get when trying to sort the data, But as I said If I uncheck one of the values, it dissapears from the list.
[URL] ........
View 5 Replies
View Related
Mar 22, 2013
If my spreadsheet has columns A-H.
A is Date
B is Room Number
C is Location
D,E,F all have numbers results in them.
G is the average of D,E,F for each person
H is group average.
If I want to define a group average in column H where it calculates the average of a group that attended the same location, date and room, for each group that attended would that be possible?
So For Example (Columns DEF left out for clarity)
Date
Rm
Location
G Inidivual Average
[Code]....
So because room number changed it calculated as a different group. Is this possible?
View 3 Replies
View Related
Nov 14, 2009
I need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
View 14 Replies
View Related
Mar 5, 2014
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
View 1 Replies
View Related
Aug 26, 2012
Excel 2007, Windows 7.
Most details are shown in the spreadsheet below. I would like it to be dynamic because the quarterly and annual data dumps I'm working with are are hundreds to thousands of lines.
Have the list be sorted, which is a part of the first attempt, would be nice but is not necessary. At this point, just being able to generate the dynamic list would be fantastic.
Excel 2007ABCDE1product lines:consist of these product subtypes2Widget series:Widget.type1Widget.type23Fidget series:Fidget.type4Gidget series:Gidget.type1Gidget.type2Gidget.type356data dump of parts sold or used in repairs7product subtype repairedpart number
[code]......
View 5 Replies
View Related
Jul 27, 2013
[URL] ......
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
View 7 Replies
View Related
Apr 23, 2008
I have a small sheet that has a number in Column A and a larger number in Column B along with some data in Columns C and D. I wish to make a separate sheet that has rows numbered using the numerical range from Column A and Column B along with the data from Columns C & D
View 2 Replies
View Related
May 2, 2008
I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.
ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.
View 9 Replies
View Related
