Formula to count # of proposals in date range then...

[lilmskitty]

Good morning you wonderful wizards!

Please help!

I have a spreadsheet that's used as a scorecard. The person that uses it is very non-computer savvy so I'm trying to make it easier for him to plug things in without me having to redo it all. So I want a summary to auto populate this Proposal close ratio on a monthly basis.

The context is this: Column E contains the date the proposal was submitted, F contains the proposal sold dollar amount and G contains the date the job was billed. The summary would be as below but formatted correctly...

Month Props Submitted Props Sold Close Ratio
April 16 9 56.3%
May 9 12 133.3%
June 20 14 70.0%
Quarterly Total 45 35 77.8%

I would like the Props Submitted and Props close to automatically populate accoring to the date range...

Can you help? I've tried COUNTIF with a range but its not counting correctly.

I'd be happy to take this offline and send you my .xls file -- its making me insane!

Thanks so much!

~Kitty

 

[Dan Waterloo]

Hi Ms Kitty,

I believe you need to use array formulas. Check out the learning resources on this website if you aren't familiar with them yet. I have your dates data in columns A and B. (For this example I'm just focusing on counting the dates. You can elaborate for your actual dataset).

Submitted | Billed
03 Mar 2007 | 21 Mar 2007
15 Mar 2007 | 03 Apr 2007
18 Mar 2007 |
22 Mar 2007 | 22 Apr 2007
04 Apr 2007 | 10 Apr 2007
05 Apr 2007 | 12 Apr 2007
06 Apr 2007 | 02 May 2007
09 Apr 2007
10 Apr 2007

Then in cells D1:G3 I have
Month | Tot_Submitted | Tot_Billed | Pct
Mar 2007 | 4 | 1 | 25%
Apr 2007| 5 | 4 | 80%

as values

with {=SUM(IF(MONTH(A$2:A$20)= MONTH($D2),1,0))} as the array formula in E2.


Sorry, I must attend a meeting now.

 

[Dan Waterloo]

{=SUM(IF(MONTH(B$2:B$20)= MONTH($D2),1,0))} is the array formula in F2.
{=SUM(IF(MONTH(A$2:A$20)= MONTH($D3),1,0))} is the array formual in E3.
{=SUM(IF(MONTH(A$2:A$20)= MONTH($D3),1,0))} is the array formula in F3.

Then the percentage formulas are regular:
=F2/E2 in G2
=F3/E3 in G3