Macro For Counting / Summarizing Data Per Month
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
ADVERTISEMENT
Mar 24, 2009
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
View 3 Replies
View Related
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 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
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 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
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
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 30, 2008
I am trying to set up a grid to count the number of orders each rep writes for each month of the year. I have tried three different formulas without success.
=COUNTIFS('Daily Compliance'!$B:$B,$C$7,'Daily Compliance'!$O:$O,">39447")-J7
In which J7 would count all orders written past Jan 31, minus K7, etc.
I also tried:
=COUNTIFS('Daily Compliance'!$B:$B,$C$7,'Daily Compliance'!$O:$O,">39447",'Daily Compliance'!$O:$O,"<39479"))
and:
=SUMPRODUCT(--('Daily Compliance'!O:O>=E274),--('Daily Compliance'!O:O<=F274),COUNTIF('Daily Compliance'!B:B,C7))
Daily Compliance is the sheet that lists all the orders, with column "B" being the rep, and column "O" being the date.
At this point, I'm not sure what I'm doing wrong.
View 9 Replies
View Related
Jan 9, 2007
I have a spreadsheet with column B being the date when a staff joined the company; column C being the date when the staff left the company. If I want to set a formular in column D to calculate just the number of month had a staff been working in the company, how should I set?
e.g any day joined in Aug 05 and left in Sep 05 will be counted as 1 month
View 9 Replies
View Related
Jul 6, 2012
I have collumn with different dates. I would like to count number of date with specific month in them (for example "june"). I tried it with if function, but something is missing.
Example of data:
1.6.2012
3.6.2012
9.6.2012
30.6.2012
[Code]...
My function: =SUMPRODUCT(IF(A1:A9;IF(MONTH(A1:A9)=6;1;0))) what shoud I correct or of better function?
View 9 Replies
View Related
Jan 23, 2009
I have items arriving on certain dates (the dates are listed in column N)
In the cell to the right of each month, I want to have a formula that will count the dated cells in column N that fall under each month.
i.e. In cell B5, next to January, I want to display the total number of cells that contain a date in January (ultimately giving me the number of items that arrive in January) the number would read '5' because there are 5 dates in January that are in the list.
View 9 Replies
View Related
Oct 21, 2009
I have a column of dates and want to count how many times there is an entry for a particular month. example:
(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009
I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.
=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.
View 4 Replies
View Related
Oct 25, 2011
i have big sheet with lot of data and in column B there is date dd-mm-yy.
How can i count specific product (column A) based on date (column B) for each month) on sheet B.
View 7 Replies
View Related
Dec 1, 2006
I would like to count how many Mondays, etc., that shh worked.
DateDayDVMClinic
11/01/06WedshhCal Vet Neuro
11/16/06ThushhCirby Ridge
11/22/06WedshhHardin Animal
11/27/06MonshhCrossroads-DS
11/24/06FrishhBear River
11/21/06TueshhLaguna Creek
11/22/06WedshhRoseville Vet
11/19/06SunshhMissouri Flat Pet
11/20/06MonshhCrossroads-Folsom
11/20/06MonshhSierra Vet-Stockton
11/20/06MonshhAnimal Clinic-Benicia
11/22/06WedshhBlue Ravine
11/24/06FrishhCat Clinic-Folsom
11/18/06SatshhAmerican River
11/21/06TueshhIndian Creek Vet
11/28/06TueshhSlate Creek Animal
Some dates will appear twice, because that will be relative to different clinics.
View 9 Replies
View Related
Dec 15, 2008
Need a formula for counting the number of occurences of a month & year in a date column? The spreadsheet is looking at items raised in any given month e.g. all items raised in Dec-08.
View 3 Replies
View Related
Aug 11, 2013
How to make information gathering easier. So I have a spreadsheet with information in one tab and Graphs & tables in another. I am trying to count how many times a word appears in my last column (the word is "HM - GM") but I only want to count how many times that appears in the month of Jul (The July column is my first column, it shows as "Jul-13" but when you click on the column it appears as 01/07/2013). I have tried a few =COUNTIFS formulas I found around the site, but none seem to work for me. I am trying to get the counted information into a table so it will show how many 'HM - GM' were in Jul-13.
View 5 Replies
View Related
Jan 13, 2014
I am trying to create a macro (pretty new) to take a sheet of data and out put the average of each column by date. The data will be filled every month and the numbers i need should be one for the 1st, the 2nd and so on for each day.
Here is a sample of the data in the sheet, i would attach a spreadsheet but cant seem to find out how.
TimeStampUTC
Air Volts
Dry Volts
Dirt
Dirt Fraction
Temp
12/1/13 0:47
4.1180
2.3714
0.5735
-0.6800
11.1674
[Code] ............
View 3 Replies
View Related
Feb 2, 2009
Please see the attached xls file so see what I am referring to.
I have shipments that are going to different destinations (rotterdam, austria, london, etc.)
I would like to count the arrival dates in column H that fall under each week's span, but ONLY IF its corresponding value in column F is 'rotterdam'.
Column C contains the ideal numbers that I would like column B's formula to return. I plan on doing this for the entire year, but if someone can some up with a formula, I might be able to modify it for the rest.
Note: This is only an example spreadsheet, I am going to be referencing an external file with much more information on it.
View 6 Replies
View Related
Aug 22, 2014
I am trying to create a graph that is conditional on two different columns. The first column is a date column, the second column has various categories. I want to show how many times each category appears per month. This database is continually added to so I wanted the formula to reflect the entire column range.
For example, let say I have 5 categories (Grapes, Apples, Peach, Pear, Banana). Column A would show a date (in a M/D/Y format) and Column B would list the fruit type. I want to show how many Grapes were input in January, February, March, etc. and then move on to show how many apples in each month, and so on.
View 5 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
May 11, 2013
I want to group data (rows) using month and year in date column using macro.
For example.
Date ID Amount
01/01/2013 12345 $100
02/01/2013 13452 $73
03/01/2013 12232 $50
04/01/2013 34232 $125
01/02/2013 12322 $67
02/02/2013 12345 $100
03/02/2013 13452 $73
04/02/2013 12232 $50
05/02/2013 34232 $125
01/03/2013 12345 $100
02/03/2013 13452 $73
03/03/2013 12232 $50
04/03/2013 34232 $125
I want to group rows by mm/yyyy in date column. Also I want sum amount column by month.
View 5 Replies
View Related
Mar 5, 2007
I am trying to create a formula that compares month over month data. If the prior month is 0 I get an error. I am having trouble with incorporating ISERR into the formula to eliminate the error.
=IF((C26-B26)/B26
View 9 Replies
View Related
Oct 2, 2006
In cell A2 on Sheet 1 = January. On sheet 2 in cell A2 I need it to = February, On sheet 3 in cell A2 I need it to = March, On sheet 4 in cell A2 I need it to = April, etc.... How can I do this with a regular text formula, not VBA coding.
View 7 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
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
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