Hi there, I was wondering if there is any body who could help me with a formula that i am having great difficulty with.
Platform: I have one spreadsheet open with three worksheets that are used. 1= phead - Contains Purchase order numbers and dates of the order but no information what was ordered. 2= pitem - Contains the purchase order number along with what was ordered but no date. 3= calc - For my workings out. This data has been exported from a visual foxpro peice of software so that we can analyse it in Excel. On the calc sheet i have created a table that shows all the records found with a specific criteria in the pitem sheet between the dates found on the phead sheet. Now it kind of works but not properly. This is due to us not writing the formula correctly. I was wondering if someone could help. This is the formula as we stand now: =COUNTIFS(pitem!$C:$C,"CP*",phead!$C:$C,">=01/01/2006",phead!$C: $C,"<=31/12/2006") It works, and gives me a result, but it is not the right result. The reason for this is it does not know how to count it. As mentioned before under platform, the data in both sheets only contain one peice of common data and that is the purchase order number for which we are not searching for. So how do i ask excel to show me: How many products that begin with CP did we purchase in the year 2006? I am sorry i have to waffle on but i want to try and explain what i was trying to achieve with what i had to start with. I have done the above formula on a CS product. I then filtered all the records on both phead and pitem to show me how many results i should have found. For some reason it said i should have 3 but my formula only showed one. Now the strange thing is that i kind of understand why but don't know it's solution. Once i have filtered the pitem table i found 3 CS005340/01 parts that were ordered in 2006 and were under 3 different purchase order numbers. So my formula should have shown 3 results. I have tried this on a new spreadsheet, but this time putting all the data to be analysed on one sheet instead of splitting it. IT WORKS! so how do i link the two sheets together? Sorry for making such a long post, but i hope there is someone out there to help. Thanks Burbonizer --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
