Create A Second Table Of Data Counting How Many Entries There Are For Each Month In Each Company

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


Counting Entries Based On Month

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)

I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.

I have made them arrays.

View 4 Replies View Related

Create A Pivot Table That Displays Data By Month?

Jan 15, 2014

I have written two macros to create a pivot table that displays data by month. I created a column that is a flag that indicates if the if an instance occurred in the last 4 months(later used as a filter). The issue is how to handle defining the last 4 months. If the current date is prior to the 16th, I want to define the last 4 months as not including the current month. If it is after the 15th, I want define the last 4 months as including the current month. Currently I have two different macros and I make the decision on which to run. I would like the macro to handle this for me. Below is the cell formula that I use to set the flag if it is in the first half of the month.

ActiveCell.FormulaR1C1 = "=IF(RC[-3]>EOMONTH(TODAY(),-5)+1,1,0)"

View 2 Replies View Related

Counting Data Entries In Rows

Apr 11, 2007

I run an online golf tour and I need a little help with the coding of my handicap excel sheet. (For any golfers out there this is a custom handicap system, not the R&A version).

First I'll explain what I have, then my problem.

I have from left to right the following, Name, Scores, Worst Score, 2nd Worst Score, Average, Hcp. (There are other columns but are not important here)

The Scores columns record each round played, so at the moment we only have 10 columns with data as its the start of the season. I currently find the "Worst Scores" from the 10 columns, as they are all relevant at the moment. My problem will arise when I get over 12 columns of data. From that point on I need to find the "HIGHEST 10" from the "LAST 12" entries.

Easy you might say, BUT the last 12 rounds played could be spread over 100+ columns and every row could be different.

Example, Player 1 could have scores in column A to AB and then in AD to AE. Where as Player 2 could have scores in every alternate column.

I'm assuming I'll have to set up some form of count to count the cells that have data in them until I get to 12. Then extract the ones I dont need.

My question is, How do I find the last 12 column cells used in any row AND extract the highest 2 from those 12?

View 9 Replies View Related

Sumifs - Calculate Amount Of Money Invoiced Per Company Per Month

Mar 25, 2014

I'm trying to calculate the amount of money invoiced per company per month. In column B I have the date the invoice was issued. In column E I have the Company name. In column G I have the amount invoiced.

View 6 Replies View Related

Excel 2003 :: How To Create Pivot Table With Multiple Columns Each Month

Apr 4, 2014

I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.

I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.

{Using Excel 2003}

NameOwnerJan JanJanFebFebFeb
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4

View 2 Replies View Related

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.


View 1 Replies View Related

Create New Column With Company Name Which Is Embedded In Email Name

Jun 12, 2014

I am working on a project. As part of project, emails were sent out to an experimental group for a cost survey. I have to combine data and do analysis now. I am given two excel spreadsheets (sample of both attached).

In sheet 1, column B contains email name. For instance, first email name (Row A2) is ‘96 '07 Bustello Third email name (Row A4) is 90-pt Panda Express '10

Based on this email name, I need to create a new column that contains only company name i.e Bustello against row A2, Panda Express against Row A4 and so on.

I thought of using ‘Text to Column’ function in the beginning. However, since there is no common character in all email names and no fixed width at which company names are embedded in email name I could not use it.

For now, I am doing it manually. I have 10,000 email names and it is becoming hard to do it manually.

Although I am given another sheet (sheet 2 of sample), which has two columns: Company number and Company Name, there is no common variable that I can use to do VLOOK UP therefore; I am not sure how to make use of that sheet.

View 7 Replies View Related

Sub-Total Table By Company & Dates

Jan 31, 2008

The file that I have attached has the run sheets of truck drivers, which company they are working for and how much money they are owed (the money they are owed is in the GST column).

What my company needs to do is to create a tracking sheet for each month for every company with a sub total of how much we owe them.

As an example, in the file I have provided, if you filter all the dates in between 1/9/07 and 1/10/07 (so all the dates for September) and then filter Company to 'TFQ'.

So what you see is all the jobs that TFQ has done for our company in September. What we want to do now is create a sub total at the end which calculates the GST column and then save it to a file, which we can then e-mail the company to say 'This is how much we owe you, is this correct'.

But since as you can imagine, filtering and adding the sub totals is a long process, is there a way to

1) Have some kind of formula or code which would filter every company within a certain date range adding the sub total and save it to a folder named 'Sep TFQ' for example. This would be the killer if it can be done.

The reason being is because the real file is much larger than i could fit here, and it would be a long process to do this manually, that is filter the company, type in the sub total, and save it to a file, as this is done every month.

View 3 Replies View Related

Sorting By Company And Date In Pivot Table

Feb 1, 2008

I have attache a file to use as a reference, the real file is over 10 000 rows long.
What I need is to be able to quickly make a summary of each company within a certain period (let's say monthly) and be able to calculate the sub total of the GST column. Every report that's created than needs to be saved and put into a seperate file e.g 'TFQ Sep 07'. I understand I need to use a Pivot Table but can some one just give me quick example of how I would do this?

View 2 Replies View Related

Macro To Create A Statistics Table From Another Data Table (containing Merged Cells)

Apr 14, 2009

I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.

1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).

2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.

3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.

4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment

5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.

View 3 Replies View Related

If And And: If Company A (A1) And Company B (B1) Both Have Indices Greater Than 125

Jun 1, 2006

I have a nested if formula that isn't working and I know there is a real simple fix.
Here is the situation: If Company A (A1) and Company B (B1) both have indices greater than 125, I want that to show that in my recommendation. For example: I used if(A1>125&B1>125,"Company A and Company B",if(A1>125,"Company A", if(B1>125,"Company B","None"))). For whatever reason, the first nested if statement isn't working correctly. what I am doing wrong with this if/and statement?

View 3 Replies View Related

Create Year To Date Sales Comparing 4 Years Month By Month?

Dec 25, 2013

Need to create year to date sales comparing 4 years month by month. Stacked chart (Excel 2010) works OK for the first three months but adding the fourth month changes the chart to 4 series with a monthly axis. To put it another way I need a vertical axis of years and a horizontal axis of $$$ with each months sales of each year stacked on its year.

View 10 Replies View Related

String Search - To Create A Combobox To Filter A Set Of Data By The Month That Is Entered

Jun 7, 2009

I am trying to create a combobox to filter a set of data by the month that is entered.

The below code worked fine when there was just the month entered, but now all the entries in the sheet are in the format 01 January 2009. So I need a section of code which will search for the combobox value as part of a string in my range.

The cbodate values are Jan, Feb, March etc ....

View 9 Replies View Related

Counting Number Of Times Same Data Appears In A Table

Jun 28, 2014

I have a table with two columns.


I would like to create another table (in a new sheet) which displays the number of times each PartNum appears in the first table.


The amount of rows in a table is variable and can reach thousands of rows.

View 2 Replies View Related

Pivot Table - Group Data By Month

May 21, 2014

I have a range of data over approx 300 rows. The first column contains dates.

I have created a pivot table and want to group the data by month. I have right clicked the table, grouped by months and selected the whole year as the date range.

For some strange reason the pivot table starts at 1/1/14 but ends at 21/2/14? It will not show the whole year.

View 1 Replies View Related

Counting Unique Entries ...

Nov 2, 2009

Couting Unique Entries

I've included a sample worksheet ...

View 10 Replies View Related

VBA Counting Used Entries In A Range

Jul 22, 2009

i need to count the number of used entries in a range.

so fex the range is A1:A10 but there are only 2 entries what i search is the number 2

is there a VBA function or so doing that.

ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..

View 4 Replies View Related

Counting Entries Between Two Dates

Apr 29, 2009

Column A contains names
Column B their unique ID number
Column C the date a transaction occurred

How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.

View 4 Replies View Related

Counting Entries Per Hour

Nov 16, 2007

My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).

How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?

View 9 Replies View Related

Counting Cell Entries ...

Aug 9, 2006

Colum A
Ticket No.
150258, 150268, 150269
150249, 150266, 150271, 150279

Colum B
No. of Stillages

I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.

View 9 Replies View Related

Counting Duplicate Entries

May 21, 2007

I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:

I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:


I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?

12345 3
12346 2

View 4 Replies View Related

Counting Entries In Date Range?

Feb 12, 2014

I'm looking for a way to count entries of numbers per day of the week within a date range. Example

Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40

I don't know if that possible in excel or its to much

View 6 Replies View Related

Counting Unique Entries From 3 Workbooks

Sep 23, 2009

I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.

I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))

View 2 Replies View Related

Counting Entries If Adjacent Box Matches

Sep 17, 2013

Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold

I have 2 columns one with staff and one with what they sold e.g.

Staff Member - Sales
Staff 1 - prod A
Staff 2 - prod B
Staff 3 - prod A
Staff 2 - prod C
Staff 1 - prod A

and have another table, the one that i want to populate, that looks like this

Staff 1
prod A =
prod B =
prod C =

Staff 2
prod A =
prod B =
prod C =


I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc

i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages

View 3 Replies View Related

Counting Entries Using Time Criteria

Jun 6, 2006

I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.

I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.

Would this be better handled with VBA?

View 3 Replies View Related

Counting Entries In A Validation Field

Apr 17, 2007

I need to count entries in a validation field

I tried using =COUNT(3,E3,M3,U3,AC3) but this does not work.

View 3 Replies View Related

Counting Number Of Multiple Entries

Jun 6, 2007

I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.

View 9 Replies View Related

Sum Dated Entries By Month

Jan 10, 2008

If all your entries are in the form of 1/9/08 and you want to sum all the data from a specific month, how would you go about doing this? I tried using sumif, but I can't get it to work.

View 5 Replies View Related

Counting Additional Entries On A Linked Spreadsheet

Sep 29, 2009

I have two worksheets, 'monthly budget' and 'summary sheet'.

Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-

=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))

I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved