Help with creating a formula

[Guest]

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

 

[Bob Phillips]

=SUMPRODUCT(--(TEXT('Monthly Enquiries'!B2:B200,"mmmm")=B$1))
=SUMPRODUCT(--(TEXT('Monthly Enquiries'!B2:B200,"mmmm")=B$1),--('Monthly
Enquiries'!D2200="Yes - full proposal"))
etc.

copy across


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[antcraw]

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