#### punukollu

##### New Member
Hello, I am looking for a formula with the following conditions:
• The fiscal year of the business runs from December 30, 2007 to January 03, 2009
• There are 13 periods in the fiscal year ending with different date ranges. For example, period 01 runs between December 30, 2007 to January 26, 2008
• There may be 3 capital projects completed in Period 01, 5 in period 02, 6 in period 03 and so on
I need a formula where I can say - count the total number of projects from period 01 to Period 13 with the period end date restrictions for the fiscal year. The formula should be able to count the no of projects for each period individually.

Thank you very much for your help.

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### iliace

##### Well-known Member
How can you determine between which dates each period runs?

How are the project completions listed?

#### punukollu

##### New Member
Accounting department sets the date ranges for each period.

The real estate department sets the date for the opening of the capital project.

#### iliace

##### Well-known Member
How can you determine between which dates each period runs?

How are the project completions listed?

Is there a list somewhere? Or can you get one into your spreadsheet somewhere?

#### punukollu

##### New Member
Hey, Thank you for trying to help me...

Yes I do have a list. For example:

Jan 10, 2008 - Store No: 0007 open
Jan 15, 2008 - Store No: 1658 open....etc....

#### iliace

##### Well-known Member
Hey, Thank you for trying to help me...

Yes I do have a list. For example:

Jan 10, 2008 - Store No: 0007 open
Jan 15, 2008 - Store No: 1658 open....etc....

This is the project list. I'm assuming you mean all in one column, or is Store No and open status in a different column?

You also need a list of periods, to match the dates to periods. Do you have that somewhere, or can you get it into the workbook?

Keeping in mind that no one here has ever seen your spreadsheet, be as specific as you can - range addresses, names of worksheets, which sample lists represent what, etc.

#### punukollu

##### New Member
Column A in Excel
1245
2548
3698
2586
1256
4578

Column B in Excel
31-Jan-08
12-Feb-08
03-Mar-08
04-Apr-08
13-May-08
18-Jun-08

Period Range dates
Period 01 - January 01, 2008 - January 31, 2008
Period 02 - February 01, 2008 - February 29, 2008
Period 03 - March 01, 2008 - March 31, 2008
Period 04 - April 01, 2008 - April 30, 2008
Period 05 - May 01, 2008 - May 31, 2008
Period 06 - June 01, 2008 - June 30, 2008

Fiscal year runs from January 31, 2008 - December 31, 2008 assuming there are 12 periods in the year.

Now, you can see that there is atleast 1 project opening in every period for the first six periods of the year. I need a formula which can count the number of projects in the period for the fiscal year automatically for all 12 periods.

Thank you very much for your help.....

#### iliace

##### Well-known Member
Okay, so I have you sample data in columns A and B (A2:B7 for the six total stores) and your list of periods in column D (D2:D7 respectively). G1 is where the period number is (must be 2 digits). Here's the formula.... kinda ugly, but it works. You have to use Ctrl+Shift+Enter after you paste this into the formula bar; regular Enter won't work (array formula).

=SUMPRODUCT(--(\$B\$2:\$B\$7>=--MID(INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)), FIND("-",INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7, FIND(" ",\$D\$2:\$D\$7)+1,2),0)))+2,FIND("-",INDEX(\$D\$2:\$D\$7, MATCH(G1,MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)), FIND("-",INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)))+1)-FIND("-",INDEX(\$D\$2:\$D\$7,MATCH(G1, MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)))-2)),--(\$B\$2:\$B\$7<=--RIGHT( INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)), LEN(INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7, FIND(" ",\$D\$2:\$D\$7)+1,2),0)))-FIND("-",INDEX(\$D\$2:\$D\$7,MATCH(G1, MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)),FIND("-", INDEX(\$D\$2:\$D\$7,MATCH(G1, MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0)))+1)-1)))

Copy down as needed. If you can afford additional columns, here's how I came up with this solution.

This returns the matching text of the period (array-entered):

=INDEX(\$D\$2:\$D\$7,MATCH(G1,MID(\$D\$2:\$D\$7,FIND(" ",\$D\$2:\$D\$7)+1,2),0))

This takes that text (populated in H1) and extracts the period begin date, in J1:

=--MID(H1,FIND("-",H1)+2,FIND("-",H1,FIND("-",H1)+1)-FIND("-",H1)-2)

This extracts the period end date, in K1:

=--RIGHT(H1,LEN(H1)-FIND("-",H1,FIND("-",H1)+1)-1)

Here is the final sumproduct that returns the result:

=SUMPRODUCT(--(\$B\$2:\$B\$7>=J1),--(\$B\$2:\$B\$7<=K1))

Much simpler to edit, but uses more cells obviously. If you can live with hiding those columns, that would be your preferred solution.

#### iliace

##### Well-known Member
<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=626 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 266pt; mso-width-source: userset; mso-width-alt: 12946" width=354><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Store</TD><TD id=td_post_1599250 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80>Date</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 266pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=354>Range</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Period #</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1245

</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>31-Jan-08

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 01 - January 01, 2008 - January 31, 2008

</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">01</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>2548</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>12-Feb-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 02 - February 01, 2008 - February 29, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">02</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>3698</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3-Mar-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 03 - March 01, 2008 - March 31, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">03</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>2586</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4-Apr-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 04 - April 01, 2008 - April 30, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">04</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>1256</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>13-May-08</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 05 - May 01, 2008 - May 31, 2008</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">05</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17>4578</TD>

<TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>18-Jun-08</TD>

<TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Period 06 - June 01, 2008 - June 30, 2008</TD>

<TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">06</TD></TR></TBODY></TABLE>

#### punukollu

##### New Member
Thank you very much. I will get to work tomorrow and will test it out and will keep you posted.

You are the best buddy!

Replies
3
Views
71
Replies
8
Views
111
Replies
1
Views
326
Replies
7
Views
249
Replies
3
Views
332

1,191,553
Messages
5,987,240
Members
440,086
Latest member
Mahi786

### 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