Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with creating a formula
Hi
I hope someone out there can help me as this is driving me mad! I am trying to sort out some formulas that will pick up information from our monthly enquiries work sheet to provide a summary on the monthly summary sheet. Both sheets are shown below... Monthly Enquiries Sheet Ref Month Client Information sent Result 601 May-06 Wiseman Lee Solicitors Yes - Fastquote Pending 602 May-06 Threadneedle Yes - full proposal Lost 603 May-06 Llyods Yes - full proposal Won 604 June-06 Harpers No N/A 605 June-06 Engineering & Gauge Yes - Fastquote Pending 604 July-06 Smith & Sons Yes - full proposal Won 604 July-06 Inmarsat No N/A The summary sheet is as follows... May June July Monthly Enquiries 3 2 2 Full Proposal issued 2 0 1 Fast Quote issued 1 1 0 Nothing issued (No) 0 1 2 Enquiries won 1 0 1 Enquiries lost 1 0 0 Enquiries pending 1 2 1 Enquiries N/A 1 2 1 Ideally I need formulaes to fill in the numbers on the second sheet automatically based on the criteria from the first sheet. Any help really gratefully received. Yours hoping for some help! Peter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with creating a formula
Peter Barker wrote:
Hi I hope someone out there can help me as this is driving me mad! I am trying to sort out some formulas that will pick up information from our monthly enquiries work sheet to provide a summary on the monthly summary sheet. Both sheets are shown below... Monthly Enquiries Sheet Ref Month Client Information sent Result 601 May-06 Wiseman Lee Solicitors Yes - Fastquote Pending 602 May-06 Threadneedle Yes - full proposal Lost 603 May-06 Llyods Yes - full proposal Won 604 June-06 Harpers No N/A 605 June-06 Engineering & Gauge Yes - Fastquote Pending 604 July-06 Smith & Sons Yes - full proposal Won 604 July-06 Inmarsat No N/A The summary sheet is as follows... May June July Monthly Enquiries 3 2 2 Full Proposal issued 2 0 1 Fast Quote issued 1 1 0 Nothing issued (No) 0 1 2 Enquiries won 1 0 1 Enquiries lost 1 0 0 Enquiries pending 1 2 1 Enquiries N/A 1 2 1 Ideally I need formulaes to fill in the numbers on the second sheet automatically based on the criteria from the first sheet. Any help really gratefully received. Yours hoping for some help! Peter Hi Peter, Here is an attempt to solving your request: I am not sure how much control you have over formatting the 'Monthly Inquiries" sheet. Here are some things to consider when using the formulas below. 1. I separated the 'Information sent' column into 2 columns. One, I labeled 'Information 2. I set the 'Month' column format on the 'Monthly Inquiries' to text. 3. Make sure that any values you are trying to summarize from the 'Monthly Inquiries' sheet are consistent, e.g. the different information types are always entered as "Full Proposal", "Fast Quote", etc. (If a pre-defined list exists, you may want to consider a drop-down set up through the 'Data Validation' feature). Any spelling and variation of values entered may cause errors. If you are able to incorporate above suggestions, then the sheet set up and formulas suggested below may be a solution to your quest. Column headings (with an example) for the 'Monthly Inquiries" sheet Ref Month Client Information sent Information Type Result 603 May-06 Llyods Yes Full Proposal Won Row headings for the 'Summary' sheet Monthly Inquiries Full Proposal Fast Quote N/A Won Lost Pending N/A Monthly Inquiries: COUNTIF(Sheet2!$B$2:$B$8,B$1&"-06") Full Proposal (and all subsequent Information types): =SUM((Sheet2!$E$2:$E$8=$A3)*(LEFT(Sheet2!$B$2:$B$8 ,LEN(Sheet2!$B$2:$B$8)-(LEN(Sheet2!$B$2:$B$8)-FIND("-",Sheet2!$B$2:$B$8))-1)=B$1)) Won (and all subsequent results): =SUM((Sheet2!$F$2:$F$8=$A7)*(LEFT(Sheet2!$B$2:$B$8 ,LEN(Sheet2!$B$2:$B$8)-(LEN(Sheet2!$B$2:$B$8)-FIND("-",Sheet2!$B$2:$B$8))-1)=B$1)) -- An alternate (and shorter) formula for the results values could be: =SUM((Sheet2!$F$2:$F$8=$A12)*(Sheet2!$B$2:$B$8=B$1 &"-06")) One last thing, make sure to adjust the ranges (might want to consider named ranges for easier reading of the formulas. HTW. Regards, A. Crawford |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help on creating a formula in excel | Excel Worksheet Functions | |||
Creating a complicated formula | Excel Worksheet Functions | |||
Creating charts from formula results | Excel Discussion (Misc queries) | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Creating a specific formula | New Users to Excel |