Countif Function? 


Countif Function?  Excel 
View Answers 
I am having trouble writing a function statement in Excel 2007. My unfamiliarity with software is hurting me.
My worksheet contains 3 columns: Date (Date), UID (Alphanumeric String), Work Order (Integer).
There can be multiple unique work orders per UID.
What I would like to do is add a column named "Repeat" that would display a 1 if the Next Work Order per UID is within 7 days.
I have tried some if statements, match, countif, etc. My problem is (think at least) I can find (in multiple ways) which UID have repeat work orders, but I cannot figure out to determine if the next work order occured 7 days later.
My worksheet contains 3 columns: Date (Date), UID (Alphanumeric String), Work Order (Integer).
There can be multiple unique work orders per UID.
What I would like to do is add a column named "Repeat" that would display a 1 if the Next Work Order per UID is within 7 days.
I have tried some if statements, match, countif, etc. My problem is (think at least) I can find (in multiple ways) which UID have repeat work orders, but I cannot figure out to determine if the next work order occured 7 days later.
Similar Excel Video Tutorials
OR Criteria Counting 3 Examples COUNTIF, SUMPRODUCT & SUMCOUNTIF
 See three formulas for counting blanks or Not Started (OR criteria):
1. COUNTIF + COUNTIF
2. SUMPRODUCT
3. SUM COUNTIF with array const ...
1. COUNTIF + COUNTIF
2. SUMPRODUCT
3. SUM COUNTIF with array const ...
COUNTIF & Ampersand
 See how to get criteria from four (4) different cells and combine (join) them into one criterion for the COUNTIF function. See how to use the COUNTIF ...
COUNTIF function and Multiplying
 See how to use the COUNTIF function to count criteria and then use the function result in a formula for multiplying ...
COUNTIF function formula 21 Examples
 See methods for counting: specific words, specific numbers, numbers greater than a hurdle, numbers between two values, values than are NOT equal to an ...
Helpful Excel Macros
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Complete Guide to Printing in Excel Macros  PrintOut Method in Excel
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
 This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
WEEKNUM UDF for Excel Versions That Don't Contain This Function  UDF
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
 This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Return the ISO Standards Start of the Year in Excel  First Monday of the Year  UDF
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
 Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Similar Topics
How could I determine the average of work orders that are received per
business days in a given month? I have two columns: Column B lists a unique
code for each work order and Column C lists the date in which each work order
was created. I don't receive work order every day, but in some days I receive
any number of work orders.
Thank you for your help.
Orlando
business days in a given month? I have two columns: Column B lists a unique
code for each work order and Column C lists the date in which each work order
was created. I don't receive work order every day, but in some days I receive
any number of work orders.
Thank you for your help.
Orlando
We use work order worksheets in excel with basic information: Date in, work order #, account, date out, total # of days, work conducted by, number of samples, description of sample, etc. Each work order has an individual worksheet. The information in these worksheets eventually get entered into a work order log containing the same information in order to compile a quarterly report. We might have 400 work orders in a quarter.
My question is: How do I link the individual worksheet to the work order log in order to get the data to transfer automatically when a new work order is entered into a worksheet? I'm able to get the first work order to link to the log worksheet, but CAN NOT figure out how to get subsequent work orders to enter into the following rows, rather than just replace the first row over and over.
Probably a pretty basic question, but I'm fairly new to any kind of programing. PLEASE HELP! Any help would be greatly appreciated. If my question is unclear, I'll try to clarify.
Thanks in advance.
ltlbt
My question is: How do I link the individual worksheet to the work order log in order to get the data to transfer automatically when a new work order is entered into a worksheet? I'm able to get the first work order to link to the log worksheet, but CAN NOT figure out how to get subsequent work orders to enter into the following rows, rather than just replace the first row over and over.
Probably a pretty basic question, but I'm fairly new to any kind of programing. PLEASE HELP! Any help would be greatly appreciated. If my question is unclear, I'll try to clarify.
Thanks in advance.
ltlbt
Have a tough problem here, not sure if it can be solved with formula or if it needs vba or what, I'm at at a loss.
here is the problem:
I can only bill my customer every 90 days for 1 order no matter how many order I fulfill. If he orders 100 orders within 90 days I can only bill him for the first one. I have a big list that I need to apply this formula to and determine how many billable order I have for each customer. I can't upload excel file from work, but will try to do it from home. Below is the set of data. I've manually calculated the 'Days since last billed order' for illustration purposes of how it should work, I need a formula that can do this since I have thousands of records every week. Driving the 'Bill' of yes or no is easy with an if statement once the 'Days since last billed order?' column is calculated. As you can see Days since last billed order? will have to self reset to 0 once it's determined that it's a new customer. the only data that I have are the first 3 columns then I need to find a solution to generate output for last two columns. Any help or guidance anyone here can provide is greatly appreciated.
Customer Order Number Order Date Days since last billed order? Bill?
1794 ABC123 4/11/2013 0 Yes
1794 ABC1234 7/1/2013 81 No
4292 ABC1235 4/19/2013 0 Yes
4292 ABC1236 6/18/2013 60 No
4292 ABC1237 7/10/2013 82 No
4292 ABC1238 7/25/2013 97 Yes
4292 ABC1239 7/30/2013 5 No
4292 ABC1240 8/1/2013 7 No
4292 ABC1241 9/1/2013 38 No
4292 ABC1242 12/1/2013 129 Yes
3333 236 3/1/2012 0 Yes
3333 236 4/1/2012 31 No
3333 236 5/1/2012 61 No
3333 236 6/1/2012 92 Yes
3333 236 7/1/2012 30 No
I am trying to calculate the number of days between a prior date and today, however, I want Excel to return a blank cell if the date is missing. I am calculating the number of days orders have been open (020, 2130, 3160, 6190, and 90+). One order may have multiple items and the way I have the worksheet laid out is one row per part number. Each item on an order will all have the same document date  here is where my problem comes in. When I add the formula it calculates each item instead of consolidating them on the order number level. I want to be able to SUM the values in the aging columns to get a total number of orders and then break that down by how many orders in each aging category. In order to get this my logic says to delete the DOC DATE and DAYS OUT formulas from all but the first line of an order. So far I have been unable to get Excel to calculate the 020 days column correctly. I have attached a copy of the SS.
this is the formula that I am currently using.
=DATEDIF(C179,'[allocation report 2211.xls]Sheet2'!$A$1,"d") < this is the formula in col E to return the number of days since the order was opened.
This is the one currently in col G "021 days " < when the date field is blank this returns a value of 40577 which is causing this formula to put this in the 90+ column.
=IF(OR(E178=0,E178>=21),"","1")
to recap here is what I am wanting to see:
1. if the date cell (C) is blank I want cell (E) {datedif form} to be blank
2. if the date cell (C) is blank I want cell (G) {021 days} to also be blank
3. if col G is SUMMED I want to see how many orders are in that category  same for columns H, I, J, and K
4. I also want to SUM col G:K to give me the total number of open orders
this is the formula that I am currently using.
=DATEDIF(C179,'[allocation report 2211.xls]Sheet2'!$A$1,"d") < this is the formula in col E to return the number of days since the order was opened.
This is the one currently in col G "021 days " < when the date field is blank this returns a value of 40577 which is causing this formula to put this in the 90+ column.
=IF(OR(E178=0,E178>=21),"","1")
to recap here is what I am wanting to see:
1. if the date cell (C) is blank I want cell (E) {datedif form} to be blank
2. if the date cell (C) is blank I want cell (G) {021 days} to also be blank
3. if col G is SUMMED I want to see how many orders are in that category  same for columns H, I, J, and K
4. I also want to SUM col G:K to give me the total number of open orders
I have a formula in a worksheet that lists equipment in inventory. It looks at another worksheet of service work orders and verifies that, if work was done on a particular unit, do a look up and post the latest meter reading recorded on a work order for that unit.
My formula works fine unless the latest work order doesn't have a meter reading posted yet, so it returns a zero  overwriting any previous meter reading that was stored there.
Code:
How do I get the most current reading...as long as it isn't a zero?
My formula works fine unless the latest work order doesn't have a meter reading posted yet, so it returns a zero  overwriting any previous meter reading that was stored there.
Code:
=IF(COUNTIF('Work Orders'!$H$3:$H$1500,$C38),LOOKUP(2,1/('Work Orders'!$H$3:$H$1500=$C38),'Work Orders'!$O$3:$O$1500),"")
How do I get the most current reading...as long as it isn't a zero?
I have two separate datasheet queries that are used to build two separate forms. In both forms, I wants users to be able to update data that comes from the Datasheet query.
The first query I built works exactly as intended.
Code:
The second query does not. I am able to view the data that I want to see, but cannot update or modify any of the fields.
Code:
If anyone could share some thoughts with me on this, it would be greatly appreciated. In order for my forms to function the way I intend them to, I will need this query to allow me to update fields in the query.
Thank You.
The first query I built works exactly as intended.
Code:
SELECT [Work Station].[Work Station], [Work Orders].[Work Order], [Work Orders].PartNum, [WO Sequence].Sequence, [WO Sequence].Machine, [WO Sequence].[Order Qty], [WO Sequence].Balance, [WO Sequence].Assignment FROM [Work Station] INNER JOIN ([Work Orders] INNER JOIN [WO Sequence] ON [Work Orders].[Work Order] = [WO Sequence].[Work Order]) ON [Work Station].[Work Station] = [WO Sequence].[Work Station];
The second query does not. I am able to view the data that I want to see, but cannot update or modify any of the fields.
Code:
SELECT [Shift Work Done].EmpNum, [Shift Work Done].Date, [Shift Work Done].WONum, [Work Orders].PartNum, [Shift Work Done].Seq, [Shift Work Done].[Hours Worked], [Shift Work Done].PartsCompleted, [Shift Work Done].PartsScrapped, [WO Sequence].[Order Qty], [WO Sequence].Balance, [WO Sequence].[Parts Per Hour] FROM ([Work Orders] INNER JOIN [Shift Work Done] ON [Work Orders].[Work Order] = [Shift Work Done].WONum) INNER JOIN [WO Sequence] ON ([Shift Work Done].Seq = [WO Sequence].Sequence) AND ([Work Orders].[Work Order] = [WO Sequence].[Work Order]);
If anyone could share some thoughts with me on this, it would be greatly appreciated. In order for my forms to function the way I intend them to, I will need this query to allow me to update fields in the query.
Thank You.
I have been working on some sort of solution for this for a couple hours now and my head hurts :)
Basically we have a scrap report that is made on a daily basis that takes all of the work orders for the day and gives all of the dirt on them.
I have 4 columns of data
WO......SKU.....Scrap %.....Scrap Total

 Anywhere from 80 to 140 rows a day...

WO......SKU.....Scrap %.....Scrap Total Subtotals &date
The problem we face now is we have over two years of work order data that we want work order counts for. I need to find something that will automate the daily work order count to save me from doing it all manually. The date for the work orders is always located down by the subtotals but the amount of works orders for each day is never the same.
I have tried everything I can think of but am stumped at this point.
Please help! :D
Basically we have a scrap report that is made on a daily basis that takes all of the work orders for the day and gives all of the dirt on them.
I have 4 columns of data
WO......SKU.....Scrap %.....Scrap Total

 Anywhere from 80 to 140 rows a day...

WO......SKU.....Scrap %.....Scrap Total Subtotals &date
The problem we face now is we have over two years of work order data that we want work order counts for. I need to find something that will automate the daily work order count to save me from doing it all manually. The date for the work orders is always located down by the subtotals but the amount of works orders for each day is never the same.
I have tried everything I can think of but am stumped at this point.
Please help! :D
Hi, not sure how to go about this, I am trying to build a scheduling calendar. My idea is to have a table with work order information. Each work order will be scheduled with a date. I would like for the days on my userform calandar to display statistics about each day. The calls to be scheduled will be broken up into 4 categories(installs, estimates, repairs and maintenance). so my table will have a field for date of service, and work type. So I would like for the calendar to be able to look up each date, then count and display the number of installs, estimates, repairs and maintenance work orders for each day. Is this possible? I am attaching my workbook...the userform that has the calandar is named "CallForm" which is a tabbed userform, the tab in question is captioned as "Monthly Calandar". Just getting started on this...have not yet made the table for the info to go in, but I will place the table on the sheet named "projects" which is hidden. Thanks in advance for any help.
DispatchMasterCopy.xls
DispatchMasterCopy.xls
I hope I don't confuse you with this question?
Currently I'm using this ARRAY to find a MAX value accoss several columns.
=MAX(FREQUENCY(IF(G6:AH6=1,COLUMN(G6:AH6)),IF(G6:AH6<>1,COLUMN(G6:AH6)))) Example: 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 = 3
0's represent the days a store orders product from a supplier. This could vary by store.
Some stores may order more often or less and on different days. 1's represent the amount of days between orders.New Challenge: The example ARRAY above displays only the days between 1 order. IF a store wants to calucalate the days between 2, 3 or 4 orders using a drop down combo box, how do I write an IF function to look back past the 1st, 2nd and 3rd 0's and still calculate the days between orders? Your assistance would be greatly appreciated.
Currently I'm using this ARRAY to find a MAX value accoss several columns.
=MAX(FREQUENCY(IF(G6:AH6=1,COLUMN(G6:AH6)),IF(G6:AH6<>1,COLUMN(G6:AH6)))) Example: 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 0 1 1 1 0 1 1 = 3
0's represent the days a store orders product from a supplier. This could vary by store.
Some stores may order more often or less and on different days. 1's represent the amount of days between orders.New Challenge: The example ARRAY above displays only the days between 1 order. IF a store wants to calucalate the days between 2, 3 or 4 orders using a drop down combo box, how do I write an IF function to look back past the 1st, 2nd and 3rd 0's and still calculate the days between orders? Your assistance would be greatly appreciated.
Hi everyone,
I have been trying to suss this one out for weeks and would really appreciate any help or pointers please.
I have a large spreadsheet for our orders that requires either filtering or somehow to show only the orders that haven't been paid from 30,60 and 90 days from the current date.
To make it easier i have attached a very simplified version of this spreadsheet with todays date entered as Today(). What I' am after for '30 days' is everthing filtered out that has been paid and everything that is over 30 days unpaid  so on the spreadsheet the only lines displayed would be Mrs K and Mrs T.
From then i could change it to 60 days and 90 Days accordingly....i think.
Ideally the other tabs would be the neatest way to display the information (i.e one tab named '30 Days' would display everything from the order sheet unpaid over 30 days).
Any help would be brilliant as at the moment i can filter out all the orders that haven't been paid but have to scroll up and down hundreds of lines to see what the dates are so we can work out who is over 30 days etc.
Thanks for any help
Mike
I have been trying to suss this one out for weeks and would really appreciate any help or pointers please.
I have a large spreadsheet for our orders that requires either filtering or somehow to show only the orders that haven't been paid from 30,60 and 90 days from the current date.
To make it easier i have attached a very simplified version of this spreadsheet with todays date entered as Today(). What I' am after for '30 days' is everthing filtered out that has been paid and everything that is over 30 days unpaid  so on the spreadsheet the only lines displayed would be Mrs K and Mrs T.
From then i could change it to 60 days and 90 Days accordingly....i think.
Ideally the other tabs would be the neatest way to display the information (i.e one tab named '30 Days' would display everything from the order sheet unpaid over 30 days).
Any help would be brilliant as at the moment i can filter out all the orders that haven't been paid but have to scroll up and down hundreds of lines to see what the dates are so we can work out who is over 30 days etc.
Thanks for any help
Mike
Hi guys
I'm working on an order log which is relyig on some countifs to keep track of it all. The countif's I'm trying to get sorted a
Countif a cell's date is more than 7 days historic of today's date but anything 7 days or less isn't counted.
Countif of the number of orders in different statuses based on today's date. These need to stay at the last figure when the date changes so I can track the historic movements of the orders.
The final one is to count a blank cell if a cell in another column is not blank.
I know this is a lot to ask but I was wondering if it was possible in Excel 2003.
Many thanks
Chris
I'm working on an order log which is relyig on some countifs to keep track of it all. The countif's I'm trying to get sorted a
Countif a cell's date is more than 7 days historic of today's date but anything 7 days or less isn't counted.
Countif of the number of orders in different statuses based on today's date. These need to stay at the last figure when the date changes so I can track the historic movements of the orders.
The final one is to count a blank cell if a cell in another column is not blank.
I know this is a lot to ask but I was wondering if it was possible in Excel 2003.
Many thanks
Chris
Hello,
I am trying to get a loop going. I have a worksheet with a lot of purchase orders with unique order IDs. When a customer orders multiple items through the website, the order is imported in to excel on multiple rows i.e. if a customer orders a black ink cartridge and a color one, the order is imported in to excel on two rows. All the customer information is the same and so is the order number. the only difference is the item name and sku. What I want to be able to do is basically have a macro that goes down the list and counts the number of rows with the same order number and then I have two columns with two different prices. I want to divide those prices by the number of rows counted then move on to the next order number and count the number of rows with that same order number and repeat the process. Let me know if that made any sense at all.
Thanks!
I am trying to get a loop going. I have a worksheet with a lot of purchase orders with unique order IDs. When a customer orders multiple items through the website, the order is imported in to excel on multiple rows i.e. if a customer orders a black ink cartridge and a color one, the order is imported in to excel on two rows. All the customer information is the same and so is the order number. the only difference is the item name and sku. What I want to be able to do is basically have a macro that goes down the list and counts the number of rows with the same order number and then I have two columns with two different prices. I want to divide those prices by the number of rows counted then move on to the next order number and count the number of rows with that same order number and repeat the process. Let me know if that made any sense at all.
Thanks!
Apoligies if this is the wrong place, but basically, I need a hand and I'm not certain as to where to look.
I'm an ALevel ICT student and have an Excel related issue regarding the user of "=CountIf" or nested "=Count(IF...)", or alternatives to the two.
Essentially, I'm trying to count the number of an item sold in a sheet in a particular month.
E.g. How many Red Tshirts are sold in January, then in February, and so on.
However, I just can't get my head around how to get it to work, and my teacher has been little/no help.
I've tried "=COUNT(IF(Orders!C12:J22="Red Tshirt", IF(MONTH(Orders!N12:N22)=1)))"
Where "Orders!C12:J22" is the Items sold
And "Orders!N12:N22" are the dates at which each order was placed.
But these clearly don't work.
I apolagise in advance for the horrifically syntaxed formula above.
Thank you for any help.
I'm an ALevel ICT student and have an Excel related issue regarding the user of "=CountIf" or nested "=Count(IF...)", or alternatives to the two.
Essentially, I'm trying to count the number of an item sold in a sheet in a particular month.
E.g. How many Red Tshirts are sold in January, then in February, and so on.
However, I just can't get my head around how to get it to work, and my teacher has been little/no help.
I've tried "=COUNT(IF(Orders!C12:J22="Red Tshirt", IF(MONTH(Orders!N12:N22)=1)))"
Where "Orders!C12:J22" is the Items sold
And "Orders!N12:N22" are the dates at which each order was placed.
But these clearly don't work.
I apolagise in advance for the horrifically syntaxed formula above.
Thank you for any help.
This formula has been working fine for me...until I have the condition where no value >0 has been entered in my LOOKUP range as yet. Or at least that is what I have managed to test so far. I've verified that my values have the same format (General) and I've verified that my ranges are all of the same size.
I'm looking in one worksheet for a work order meter reading that will be entered in another sheet when the Unit # for the equipment matches. I discovered if there is a work order entered for and no meter reading has ever been recorded yet, I get the #N/A error. If I go in and put a fictitious meter reading in of 1, the error goes away. It doesn't like a blank or zero meter reading and I need to know how to get around this.
Here's the current calculation:
Code:
I believe I've been able to narrow down what is probably the cause, I just don't know how to fix it.
I'm looking in one worksheet for a work order meter reading that will be entered in another sheet when the Unit # for the equipment matches. I discovered if there is a work order entered for and no meter reading has ever been recorded yet, I get the #N/A error. If I go in and put a fictitious meter reading in of 1, the error goes away. It doesn't like a blank or zero meter reading and I need to know how to get around this.
Here's the current calculation:
Code:
=IF(COUNTIF('Work Orders'!$G$3:$G$1500,$C77),LOOKUP(2,1/(('Work Orders'!$G$3:$G$1500=$C77)*('Work Orders'!$P$3:$P$1500<>0)),'Work Orders'!$P$3:$P$1500),"")
I believe I've been able to narrow down what is probably the cause, I just don't know how to fix it.
I cannot figure out why the following is not working...
=IFERROR(INDEX('Work Orders'!$C:$C,SMALL(IF(AND('Work Orders'!$A:$A=$P$2,'Work Orders'!$B:$B=$L$3),ROW('Work Orders'!$C:$C)MIN(ROW('Work Orders'!$C:$C))+1),2)),"")
I can get this to work with just the IF statement:
=IFERROR(INDEX('Work Orders'!$D:$D,SMALL(IF('Work Orders'!$A:$A=$P$2,ROW('Work Orders'!$D:$D)MIN(ROW('Work Orders'!$D:$D))+1),1)),"")
=IFERROR(INDEX('Work Orders'!$D:$D,SMALL(IF('Work Orders'!$B:$B=$L$3,ROW('Work Orders'!$D:$D)MIN(ROW('Work Orders'!$D:$D))+1),1)),"")
Both return the correct value. When I use the AND statement, it errors out. Any help would be appreciated.
I am trying to create a formula using index, match, and/or offset but I just can't get it to work. The lookup value is a single cell in one worksheet, the data are in another. The problem is that I want to look up a production work order number and then go down and over for specific values.
Here's a brief example: Say the work order number is 123.
In the second worksheet I have:
Work Order No. 123
Production StepsMachineDue Date
GenerationGP12/28/07
GrindingGP22/29/07
PolishingGP34/1/07
QAGP44/5/07
Work Order No. 456
Production StepsMachineDue Date
GenerationGP33/10/07
GrindingGP53/15/07
PolishingGP64/5/07
QAGP24/15/07
Work Order No. 789
Work Order No. 789
Production StepsMachineDue Date
GenerationGP23/9/07
GrindingGP123/29/07
PolishingGP44/1/07
QAGP64/5/07
I want to create a work order traveler that is set up with the same items listed above. I want to be able to look up work order no. 123 and then pull in the data that goes with that particular work order. The search should go through ALL of the work orders listed (I've shown three here  there are dozens) and return the data for the work order that I've entered on the traveler.
I've checked out a lot of online ideas but none of them quite fit. Any help will be greatly appreciated!
Here's a brief example: Say the work order number is 123.
In the second worksheet I have:
Work Order No. 123
Production StepsMachineDue Date
GenerationGP12/28/07
GrindingGP22/29/07
PolishingGP34/1/07
QAGP44/5/07
Work Order No. 456
Production StepsMachineDue Date
GenerationGP33/10/07
GrindingGP53/15/07
PolishingGP64/5/07
QAGP24/15/07
Work Order No. 789
Work Order No. 789
Production StepsMachineDue Date
GenerationGP23/9/07
GrindingGP123/29/07
PolishingGP44/1/07
QAGP64/5/07
I want to create a work order traveler that is set up with the same items listed above. I want to be able to look up work order no. 123 and then pull in the data that goes with that particular work order. The search should go through ALL of the work orders listed (I've shown three here  there are dozens) and return the data for the work order that I've entered on the traveler.
I've checked out a lot of online ideas but none of them quite fit. Any help will be greatly appreciated!
Hello all!
I am trying to cut down on a lot of manual input. Right now I enter in values everyday by finding specific work orders and copying a few lines of information from each one. Everday the work orders are different and each work order is its own excel file.
I would like, if possible, to use a vlookup whose array covered an entire folder of excel files, not just workbooks within one excel file. I would then only have to type in the work order number, and the equation would go searching for the file title with that number in it. All of our work orders are made from a premade template so everything I need are always in the same spots in every work order.
Any advice would be greatly appreciated.
I am trying to cut down on a lot of manual input. Right now I enter in values everyday by finding specific work orders and copying a few lines of information from each one. Everday the work orders are different and each work order is its own excel file.
I would like, if possible, to use a vlookup whose array covered an entire folder of excel files, not just workbooks within one excel file. I would then only have to type in the work order number, and the equation would go searching for the file title with that number in it. All of our work orders are made from a premade template so everything I need are always in the same spots in every work order.
Any advice would be greatly appreciated.
This is difficult to explain which is why i uploaded my file but basically i have an order book. The orders have classifications  emergency, plan, stock and specified. The order number is listed beside each part in the order as some orders have multiple parts. The dispatch date is then noted down against each part of the order  only when all parts of the order have shipped can i class the order as shipped. I then measure these shipped orders against their classification in cells J62 to 65.
The equation is three fold, it needs to check order classification, identify the unique order numbers, then recognise when all parts of an order have a date noted. It may be a variation on the formula used to count the orders in each class =COUNT(1/FREQUENCY(IF($C$11:$C$44="Emergency",MATCH($J$11:$J$44,$J$11:$J$44,0)),ROW($J$11:$J$44)ROW($J$11)))
After i achieve this i want to count all orders shipped in Jan, Feb, March etc
Cannot anyone help  i'm close to finishing this but as you can see from my sheet the best i have managed so far is classing an order as shipped even when 1 of its parts has shipped. The two results boxes reflects the 2 methods i have used to get my results.
Thanks
The equation is three fold, it needs to check order classification, identify the unique order numbers, then recognise when all parts of an order have a date noted. It may be a variation on the formula used to count the orders in each class =COUNT(1/FREQUENCY(IF($C$11:$C$44="Emergency",MATCH($J$11:$J$44,$J$11:$J$44,0)),ROW($J$11:$J$44)ROW($J$11)))
After i achieve this i want to count all orders shipped in Jan, Feb, March etc
Cannot anyone help  i'm close to finishing this but as you can see from my sheet the best i have managed so far is classing an order as shipped even when 1 of its parts has shipped. The two results boxes reflects the 2 methods i have used to get my results.
Thanks
What I'm trying to do is add strings with multiple values, for example;
in one cell, user enters 1F, then in another cell they enter 0.4F, I want 1.4F when I sum them up. Basically I'm counting special hours of work for each month (normal hours don't have the "F" which I have no problems with).
I'm also calling upon other worksheets, so below is a small chunk of the code I'm using. The problem with this method is that I need to manually input the value for each string.
(COUNTIF(JAN!B2:JAN!AF2,"1F") + COUNTIF(FEB!B2:FEB!AC2,"1F") + COUNTIF(MAR!B2:MAR!AF2,"1F") + COUNTIF(APR!B2:APR!AE2,"1F") + COUNTIF(MAY!B2:MAY!AF2,"1F") + COUNTIF(JUN!B2:JUN!AE2,"1F") + COUNTIF(JUL!B2:JUL!AF2,"1F") + COUNTIF(AUG!B2:AUG!AF2,"1F") + COUNTIF(SEP!B2:SEP!AE2,"1F") + COUNTIF(OCT!B2:OCT!AF2,"1F") + COUNTIF(NOV!B2:NOV!AE2,"1F") + COUNTIF(DEC!B2:DEC!AF2,"1F")) + ((COUNTIF(JAN!B2:JAN!AF2,"1/2F") + COUNTIF(FEB!B2:FEB!AC2,"1/2F") + COUNTIF(MAR!B2:MAR!AF2,"1/2F") + COUNTIF(APR!B2:APR!AE2,"1/2F") + COUNTIF(MAY!B2:MAY!AF2,"1/2F") + COUNTIF(JUN!B2:JUN!AE2,"1/2F") + COUNTIF(JUL!B2:JUL!AF2,"1/2F") + COUNTIF(AUG!B2:AUG!AF2,"1/2F") + COUNTIF(SEP!B2:SEP!AE2,"1/2F") + COUNTIF(OCT!B2:OCT!AF2,"1/2F") + COUNTIF(NOV!B2:NOV!AE2,"1/2F") + COUNTIF(DEC!B2:DEC!AF2,"1/2F"))*(1/2)) + ((COUNTIF(JAN!B2:JAN!AF2,"1/4F") + COUNTIF(FEB!B2:FEB!AC2,"1/4F") + COUNTIF(MAR!B2:MAR!AF2,"1/4F") + COUNTIF(APR!B2:APR!AE2,"1/4F") + COUNTIF(MAY!B2:MAY!AF2,"1/4F") + COUNTIF(JUN!B2:JUN!AE2,"1/4F") + COUNTIF(JUL!B2:JUL!AF2,"1/4F") + COUNTIF(AUG!B2:AUG!AF2,"1/4F") + COUNTIF(SEP!B2:SEP!AE2,"1/4F") + COUNTIF(OCT!B2:OCT!AF2,"1/4F") + COUNTIF(NOV!B2:NOV!AE2,"1/4F") + COUNTIF(DEC!B2:DEC!AF2,"1/4F"))*(1/4))
This continues on for multiple fractions. Basically I'm counting each instance of the fraction, then multiplying it to get the value that each string should give me.
Btw: I don't actually like the fractions so I may just use integers instead, but the problem with that is I need to change multiple numbers as you can see.
A For loop or IF statement would be greatly appreciated, sorry if I made this difficult to understand.
I am trying to build a template and the data from my Pivot table is being used on another sheet in the workbook.
I work in production scheduling so we have a download with the start date. Problem is that multiple orders start on the same date and the first value must be the work center
So for example, order Z and order X start on the same date and run on the same work center. In the pivot table, it only lists the date once in row 1. I would like it to list the date and work center in each row in which there is a value.
Current:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 X
Ideal:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 85 4/15 X
I work in production scheduling so we have a download with the start date. Problem is that multiple orders start on the same date and the first value must be the work center
So for example, order Z and order X start on the same date and run on the same work center. In the pivot table, it only lists the date once in row 1. I would like it to list the date and work center in each row in which there is a value.
Current:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 X
Ideal:
Column A Column B Column C
Work Center Start Date Sales Order
Row 1 85 4/15 Z
Row 2 85 4/15 X
I work in a printing company where we receive orders from clients daily for print work.
We process the orders by printing the requird documents and finally pack it in a box to be ready for "Collection" by the mailing company we use.
For each box that is packed has a record in excel for:
Order no. Packed Date Packed Time Packed Day
The order can be packed on any day at any time but the Collections from the mail company are only on Tue, Thu, Fri(fixed days) at 16:30pm(fixed time), which is the main problem here for calculation
I need to calculate the collection due date, day from the Packed Date and Time information that I already have.
Example 1
Packed Date Packed Time Packed Day
01/03/09 11:43 Sunday
Then Collection should be on Tuesday as thats the closest collection day from the day packed, so it should look like this
Collection Due Date Collection Time Collection day
03/03/09 16:30 Tuesday
One more thing, If any order is packed later than 16:30 on the day of collection, then it will go on the next collection due day.
Example2
Packed Date Packed Time Packed Day
05/03/09 16:40 Thursday
So collection should be on Friday as it was packed later than 16:30 on Thursday. So it should look like this
Collection Due Date Collection Time Collection day
06/03/09 16:30 Friday
Therefore I am looking for a Function that can calculate the first Collection Date and Day(Tue, Thu, Fri) from the Packed Date and time.
Please help me with this problem, I need it solved urgently for work. I am an intermediate user so would be glad to have a solution not too difficult for my understanding. I would be extremely grateful for all your support.
SAM.
We process the orders by printing the requird documents and finally pack it in a box to be ready for "Collection" by the mailing company we use.
For each box that is packed has a record in excel for:
Order no. Packed Date Packed Time Packed Day
The order can be packed on any day at any time but the Collections from the mail company are only on Tue, Thu, Fri(fixed days) at 16:30pm(fixed time), which is the main problem here for calculation
I need to calculate the collection due date, day from the Packed Date and Time information that I already have.
Example 1
Packed Date Packed Time Packed Day
01/03/09 11:43 Sunday
Then Collection should be on Tuesday as thats the closest collection day from the day packed, so it should look like this
Collection Due Date Collection Time Collection day
03/03/09 16:30 Tuesday
One more thing, If any order is packed later than 16:30 on the day of collection, then it will go on the next collection due day.
Example2
Packed Date Packed Time Packed Day
05/03/09 16:40 Thursday
So collection should be on Friday as it was packed later than 16:30 on Thursday. So it should look like this
Collection Due Date Collection Time Collection day
06/03/09 16:30 Friday
Therefore I am looking for a Function that can calculate the first Collection Date and Day(Tue, Thu, Fri) from the Packed Date and time.
Please help me with this problem, I need it solved urgently for work. I am an intermediate user so would be glad to have a solution not too difficult for my understanding. I would be extremely grateful for all your support.
SAM.
I am using Excel 2002, I have a spreadsheet with ~200 rows and ~75 rows. Column A is sorted to be in numerical order, column b is date field mm/dd/yyyy, column C is a pull down with Y/N/NR/NA values. I need to figure out how to do a count if statement that says any date in column B with value "Y" from column C. Since the dates in Column B aren't in chronological order, sorting it first and then doing a general countif statement wont work in this situation, becuase I already have a countif statement setup based on the fact that column A is sorted in numerical order.
A B C D
1 1/1/2007 Y
2 12/15/2006 N
3 4/5/2006 Y
4 3/3/2007 NR
5 11/20/2007 Y
Desired results  COUNTIF Date range 1/1/200712/31/2007 , values in Column C
Results:
2  Y
1  NR
Thanks for helping
A B C D
1 1/1/2007 Y
2 12/15/2006 N
3 4/5/2006 Y
4 3/3/2007 NR
5 11/20/2007 Y
Desired results  COUNTIF Date range 1/1/200712/31/2007 , values in Column C
Results:
2  Y
1  NR
Thanks for helping
I am trying to create a database with due dates for work. For Example.
Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())
I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the countif and it still gets the same answer.
For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"
Please see the attached file. Any help would be greatly appreciated!!!!!
Kevin
Due in 7 Days =COUNTIF(A7:A40,"<="&TODAY()+7)COUNTIF(A7:A40,"<"&TODAY())
Due in 30 Days =COUNTIF(A7:A40,"<="&TODAY()+30)COUNTIF(A7:A40,"<"&TODAY())
Due in 60 Days =COUNTIF(A7:A40,"<="&TODAY()+60)COUNTIF(A7:A40,"<"&TODAY())
Due in 90 Days =COUNTIF(A7:A40,"<="&TODAY()+90)COUNTIF(A7:A40,"<"&TODAY())
Lapsed<Today =COUNTIF(A7:A40,"<"&TODAY())
I think these work for me in excel, but I'm not a master at this and for some reason when I read my functions I swear they are contradicting themselves. The reason I feel the functions contradict themselves is because I can remove the countif and it still gets the same answer.
For the date ranges 90, 60, 30 & 7 I do not want dates counted that are less than "today" or ex. = a negative number. They would be considered "Lapsed"
Please see the attached file. Any help would be greatly appreciated!!!!!
Kevin
I am preparing chart for sales team and given area to select them the order with clicking the cell and based on their sellect date of order I want to shown the complete credit cycle chart, I have tried but couldn't suceed, can anybody help me to develop this template ??
Details
1. delivery will be dependent on Lead time
2. Payment due based on credit days
LEGEND Distributor XYZ A1 Order given STL SDD A2 Delivery TM AAA A3 Delivery confirmation & Invoice Printing Monthly volumes (KL) 60 A4 Invoice Received No. of orders 6 A5 Payment due Lead Time (days) 5 Credit Days 7 Average order size (Rupees) 2,300,000 Credit Limit required (equivalent orders) Cash Security required (equivalent orders) Input area ( Select "YES" from the drilldown) single row per order July Aug Days M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Order # 1 Yes Order # 2 Yes Order # 3 Yes Order # 4 Yes Order # 5 Yes Order # 6 Yes Order # 7 Order # 8 Order # 9 Credit cycle chart July Aug Days M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Order # 1 A1 A2 A3 A4 A5 Order # 2 A1 A2 A3 A4 A5 Order # 3 A1 A2 A3 A4 A5 Order # 4 A1 A2 A3 A4 A5 Order # 5 A1 A2 A3 A4 A5 Order # 6 A1 A2 A3 A4 A5 Order # 7 Order # 8 Order # 9
Details
1. delivery will be dependent on Lead time
2. Payment due based on credit days
LEGEND Distributor XYZ A1 Order given STL SDD A2 Delivery TM AAA A3 Delivery confirmation & Invoice Printing Monthly volumes (KL) 60 A4 Invoice Received No. of orders 6 A5 Payment due Lead Time (days) 5 Credit Days 7 Average order size (Rupees) 2,300,000 Credit Limit required (equivalent orders) Cash Security required (equivalent orders) Input area ( Select "YES" from the drilldown) single row per order July Aug Days M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Order # 1 Yes Order # 2 Yes Order # 3 Yes Order # 4 Yes Order # 5 Yes Order # 6 Yes Order # 7 Order # 8 Order # 9 Credit cycle chart July Aug Days M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S M T W T F S S Date 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Order # 1 A1 A2 A3 A4 A5 Order # 2 A1 A2 A3 A4 A5 Order # 3 A1 A2 A3 A4 A5 Order # 4 A1 A2 A3 A4 A5 Order # 5 A1 A2 A3 A4 A5 Order # 6 A1 A2 A3 A4 A5 Order # 7 Order # 8 Order # 9
Hey Guys,
This one has me stumped. Here's my problem:
I have a pivot table that shows work orders & associated data from a SQL query. I have the pivot sorted by desending work order number. Of all the work orders that show up from the query, I only want to average the last 12 work order numbers (i.e. a running 12 work order average). "Top 10" doesn't seem to fit this scenario & I can't seem to find a way to filter these other than manually with a slicer. I'd like to have it automated a little more than that though. Anyone have any ideas that might point me in the right direction?
Any & all ideas welcome; a way to do this on the pivot table, a different query script, VBA, anything at all...