Counting Orders/Rep/Month

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


ADVERTISEMENT

Counting Number Of Month

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

Counting Number Of Dates With Month

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

Counting Dates That Fall Within A Certain Month

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

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)
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

Counting Record Based On Month?

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

Counting Mondays Worked In A Month

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

Counting Month And Year From A Date Column

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

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 View Related

Counting Word Occurrence Within A Month From Another Column

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

Counting Dates That Fall In A Specific Week Of The Month

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

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 View Related

Counting Number Of Times Specific Month Appears In Entire Column?

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

Financial Model (formula To Equally Distribute Revenue Either Over The Next 1 Month, 2 Month Or 3 Month Period Depending On Size Of The Deal)

Dec 23, 2008

I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.

Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.

- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...

View 4 Replies View Related

Assign Stock To Orders

Apr 10, 2008

I need a macro that looks at the earlier orders first and assigns inventory to them. Keep assigning inventory until either there are no more orders, or the inventory is depleted. I have tried to figure this out using formulas but it never seems to work out correctly. Here is a little sample I made to show what I am trying to do.


Item# Date Quantity Order Quantity Committed

Item# Quantity in Inventory 1 1/1/2008 18

1 21 2 1/5/2008 23

2 300 3 1/10/2008 10

View 13 Replies View Related

Orders Sheet Macro

Apr 7, 2007

I have been trying to create a macro button on my new orders page for my spreadsheet.

What I am hoping to achieve is a marco that when I click place it, it will take the information needed from the new orders page and paste it into the existing orders, but it does not end there the tricky bit im finding is how to get it to go to my stock spreadsheet and whatever part was ordered it would lower the stock by the quantity that was ordered so in effect the spreadsheet would lower my stock level as a items were purchased. I have attached the spreadsheet for you all to have a look at. I only have very basic VB skill but eager to learn. If anyone can solve this for me I defiantly owe them a pint

View 3 Replies View Related

Display And Print Orders' First Rows

Feb 10, 2009

There will be many orders in this list and each orders will have several appendixes. I want to able to see and print the orders with their first appendix row but I want to do that when I need.

and do not want to loose any data.

View 4 Replies View Related

Count How Many Orders Have Part Numbers

Mar 16, 2009

I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result.

Here's how the Data looks like:

OrderPart numbers
123A
123B
123C
123D
234A
234B
234E
346A
346C

Answer for above would be 2 orders that have A and B part numbers on the same order.

View 12 Replies View Related

Count Closed Work Orders

Sep 11, 2009

I need to count the amount of work orders that my employees have completed.

Review example.
How many work orders did chuck complete? (Column D)
How many work orders did brian complete?

View 2 Replies View Related

Match Production To Open Orders

Aug 13, 2012

I work for a manufacturing plant where we manufacture one product which is then sold under different names.

The product is made from the same material.

I am looking for a way to match up production to the orders and then subtract the produced quantity from the orders.

Tab 1 - Orders: Column 1 has the items (A1, A2 and A3 are all produced from A), Column 2 has the date when the order has to ship and Column 3 has the ordered volume.

Tab 2 - Production: Column 1 has the raw material, Column 2 has the production date and Column 3 has the amount manufactured.

Tab -1: Orders

Item
Ship Date
Ordered Qty, KG

A-1
8/22/2012
16

[Code] ..........

Tab -2: Production

Item
Production Date
Quantity

A
8/13/2012
140

[Code] ........

View 4 Replies View Related

INDEX, LOOKUP Or Find The Top 5 MAX Orders

Jan 20, 2004

Columns F looks at another table within the same worksheet for the Customer Code that has the MOST Orders in Column B.....then places the Tot Orders amount in F2..... Cust Codes in Column E and the Cum Values into Col G ( that's already been accumulated and housed in column C ).

Cell F3 then go looks up the next largest order in Column B and pulls over the adjacent Customer Code & Cum Value to the E,F,G table again.

Basically there's 3 functions:
1 in E2
1 in F2
1 in G2
All to copy downward within that table

******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2000 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFG1CustomerTot*OrdersCum*Value.CustomerTot*OrdersCum*Value2Cust*682$943.Cust*682$9433Cust*973$787.Cust*973$7874Cust*1055$525.Cust*1055$5255Cust*840$438.Cust*840$4386Cust*125$150.Cust*125$1507Cust*27$300....8Cust*55$180....9Cust*43$75....10Cust*31$25....11Cust*71$12....Sheet1*
[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Update Log Worksheet And Maintain A History Of All Orders?

Apr 20, 2013

I have a table that includes list of clients, products they order, and requested delivery date. When the client calls in to place an order, I pull up the excel table (orders worksheet) and fill in the items quantities and the required delivery date for the client who called. Once I fill the information for the client who called in, I need to update a "log" worksheet with the information that I just entered. The log worksheet will eventually contains history of all the orders which I received in the past.

View 1 Replies View Related

Identify Any Jobs That Are In Sheet1 But Not In The Current Orders Tab

Jun 30, 2009

look at the attached. I trying to identify any jobs that are in sheet1 but not in the current orders tab.

View 3 Replies View Related

Order Summary- 8 Sheets In My Worksheet With Orders From My Customers

Oct 28, 2009

I have 8 Sheets in my worksheet with orders from my customers. Column C is their PO# and Column M is the Status of the order. What im looking to do is set up a summary sheet with the List of 8 customers that tells me what PO#'s are in a particular status.
I need a formula to list all PO's that are in "Fabricating" or "Completed" Statuses.

I have gotten this far:
=if(Sheet1!M25="Fabricating",Sheet1!C25,if(Sheet1!M25="Completed",Sheet1!C25,"None"
which works perfectly for the individual rows, but i need it to do it for all rows (1-2500). If i do Sheet1!M2:M2500, i loose it.

View 14 Replies View Related

Graph Columns Of Data On Different Tabs In Different Orders On Same Plots

Feb 4, 2014

I get large data sets that are organized in columns and each has a title at the top of it. I will get several sets of this data in order to compare them all on graphs.

Now here is the kicker, the columns are not always in the same order. Not always named the same. And not always the same number of columns.

I want to make a tool to handle this to a point where I just import the data and everything is taken care of. I also want a printable report for each data set to which I can select from a drop down menu. Populate cells on a tab, and then can be printed.

View 2 Replies View Related

Macro Identifies FULL ORDERS And Hides All Other Rows

Jul 25, 2007

macro - show rows ONLY if two columns = each other AND......
Hello Excel Swammis!

I am in need of your assistance again.

I have an Excel report set up as follows:

Col D - Order #'s
Col G - part #'s
Col H - warehouse code for each part # ("N", "M", etc...)
Col K - Qty of part # ordered
Col L - Qty reserved for that part # on the order

List of orders starts on row 9.

If a particular line on the order is filled, then Col K = Col L for that row.
When the whole order is filled, then Col K = Col L for all rows on that order.

What I need is a macro that identifies FULL ORDERS and hides all other rows. So, if Col K = Col L for all rows adjacent to same order #'s in Col D (AND if Col H has value of "N" or "M" for warehouse code), it will be visible. Any orders with even ONE unfilled line will be hidden.

I am also looking for the reverse of this for a seperate macro. IE: Any orders with even ONE line not completely filled, I want all rows for those orders to be visible and all else hidden (again, only if Col H = "N" or "M").

View 9 Replies View Related

Tally All The Work Orders That Are Created And Either Completed Or Pending

Aug 16, 2008

Her situation is that she would like to be able to tally all the work orders that are created and either completed or pending. According to what she tells me she usually spends hours tallying the monthly worked on orders manually....

View 15 Replies View Related

Conditional INDEX MATCH - To Extract Companies From Orders Sheet

Apr 10, 2014

Attached spreadsheet.

Basically I have 2 sheets. The first an orders sheet which shows all orders from companies with the date of the order, whether it is a repeat order, and whether it has been delivered. The second sheet a customer account sheet which shows the order history by customer. I have used the following formula :

=IFERROR(INDEX(Orders!$B$2:$B$23,MATCH(0,INDEX(COUNTIF($A$1:A1,Orders!$B$2:$B$23),,),)),"")

To extract the companies from the orders sheet however if a company has had no previous order (i.e. a new customer) and has not recieved a delivery yet I do not want him added to the sheet.

I have tried amending my formula in different ways but always encounter problems.

View 3 Replies View Related

Excel 2003 :: Sort To Include Only Work Orders With All Numbers?

Aug 27, 2013

Using Microsoft Access to pull from a database work orders. Some of those are what we call "dummy" work orders and have a letter in them, real work orders are signified by a 10 digit number. I would like to sort and eliminate the letters to find appropriate data. I am assuming I will need to copy into excel and do some sort of formatting but not sure where to start.

View 1 Replies View Related

Sort Matching Orders Into Groups And Add Product Names To Breaklines

May 21, 2014

How do I create a macro to sort rows into groups, add break rows in between & insert a product sourced from another excel workbook onto the break line?

I receive a spreadsheet each day with a series of requests, most of the information is the addresses & names etc with the important data in columns K,M & O. Data in each of these columns is a code that refers to a product and information sheet to be sent out to the person requesting it. They may want just one in which case data will only be in column K, if they want two there will also be data in M and if they require three there will be data in column O as well. The bit I do know is how to sort the important data into groups of the same by the country (column I) and the product sku's.

Whilst I can do the sort as a recorded macro I really need it to be part of a larger macro that does the following bit I can't get my head around. I need to insert a break row above each group then by cross referencing the data in the product sku columns to a separate workbook insert the appropriate item / letter combination onto the break line row, telling the volunteer which items to send out. This is a v look up?

I have attached an example of the files we work with, the first tab is with the data simply sorted and the second is what I need it to look like. I normally receive it as a CSV file, there are around 8000 possible combinations of product and instructions and the files are normally around 4000 records long. At present we insert the break lines and copy/paste the information manually, seriously slowing down the process.

Orders Example EUR RM.xlsx

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved