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

#### lilmskitty

##### New Member
Good morning you wonderful wizards!

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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Dan Waterloo

##### Well-known Member
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

##### Well-known Member
{=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

Replies
2
Views
501
Replies
13
Views
1K
Replies
4
Views
3K
Replies
0
Views
763

1,190,681
Messages
5,982,225
Members
439,769
Latest member
trungminh2802

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back