Help with formulas
 

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

Help with formulas
 

some of this might help you get started, items used between workbooks, just
remove file name portions.

=IF(OR(V1={"","."}),"",
IF(ISNA(VLOOKUP(V1,[filename.xls]sheet!$A$1:$A$4000,1,0)),"dif",""))

=IF(OR(COUNTIF($U$100:$U$3000,U3)=1,U3={"","."),"" ,COUNTIF($U$100:$U$3000,U3))

=SUMPRODUCT(--(EXACT(LEFT($AF$56:$AF$1173,1),{"A","B","C"})))

=SUMPRODUCT(--(LEFT($AX$56:$AX$1173,1)="x"),--($G$56:$G$1173<"x"))

"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

Help with formulas
 

Have you considered using a Pivot table?

Regards.

Bill Ridgeway
Computer Solutions

"Peter Barker" wrote in message
...
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

Help with formulas
 

Try:

For enquiries per month ..

=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$8)=MONTH(B2)))

where B2 contains 01/05/2006 (1st May) formatted as Custom==mmmm

C2 will contain 01/06/06 (1st June) ....etc

Copy across for required months

For "Full proposals" per month ....

=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$8)=Month(B2)),--(ISNUMBER(SEARCH("full
proposal",Sheet1!$E$2:$E$8))))

Change "full proposal" to fix other criteria

Copy across for required months

Foe "won","lost" etc .....

=SUMPRODUCT(--(MONTH(Sheet1!$C$2:$C$8)=MONTH(B2)),--(ISNUMBER(SEARCH("won",$F$2:$F$8))))

Copy across for required months

HTH

"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