Why won't Excel autofill the DATE() function?

[Special K]

I am using the DATE() function inside of a larger function, but I notice the following problem even if I just do the following in separate cells:

=DATE(2011,1,1)
=DATE(2011,2,1)
=DATE(2011,3,1)

If I hilight those 3 cells and drag the autofill down to the next cell, it fills it with 1/1/2011 instead of 4/1/2011. Why is this? Why isn't Excel recognizing an obvious pattern here?

 

[shadow12345]

you could just highlight the range and press fill series instead... that works but i don't know why the autofill doesn't

 

[MrKowz]

Excel can't logically choose what, inside a formula, to increment unless it has a range reference.

Assuming you are entering the formula in A1, try:
=DATE(2011,ROWS($A$1:A1),1)

And copy this down

 

[Special K]

Excel can't logically choose what, inside a formula, to increment unless it has a range reference.

Assuming you are entering the formula in A1, try:
=DATE(2011,ROWS($A$1:A1),1)

And copy this down

I see how that would work, but could it be modified to a general case such that I could increment the dates as far out as I wanted to? For example, how could I generate this sequence of dates using autofill:

1/1/2011
2/1/2011
3/1/2011
4/1/2011
5/1/2011
6/1/2011
7/1/2011
8/1/2011
9/1/2011
10/1/2011
11/1/2011
12/1/2011
1/1/2012
...
and so on. It doesn't look like your formula would be able to correctly autofill the 1/1/2012.

Additionally, I need to generate a second sequence of dates that represent the last day of each month. It seems like autofilling this would be even more difficult since the last day of each month varies.

 

[RoryA]

Date is quite clever - it will roll the years over. To get the last day of a month, specify the month you want plus one, then use 0 as the day number.

 

[Special K]

Date is quite clever - it will roll the years over. To get the last day of a month, specify the month you want plus one, then use 0 as the day number.

Thanks, I verified that the DATE function behaves as you and MrKowz described it when I use it alone. Excel still can't seem to pick up the pattern when I include it in a larger expression, however. Here is the first equation:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

Here is the 3rd in the sequence:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A3),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A3)+1,0)))

If I manually type in 3 of these and then try to autofill to the 4th, here is what I get in the 4th position:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=D7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:D1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:D1)+1,0)))

As you can see, it autofills to D1 instead of A4 like it should. Is there any way to give Excel a hint as to how to autofill the sequence?

 

[xenou]

1)
I don't think you should select three cells when autofilling formulas. Select one cell and autofill/copy/drag it.

2)
Also, I'm not sure you can actually do this if your formula references cell in row 65535 (relatively) and you are dragging down. Unless you are using XL2007/XL2010.

3)
If you still have problems, construct a small dataset that illustrates the desired inputs and outputs (referencing just a few rows, not thousands of them).

 

[ukmikeb]

If I manually type in 3 of these and then try to autofill to the 4th, here is what I get in the 4th position:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=D7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:D1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:D1)+1,0)))

As you can see, it autofills to D1 instead of A4 like it should. Is there any way to give Excel a hint as to how to autofill the sequence?

Hi

You don't mention which cells you are entering these formulas in.

Are they in A1 through A4 or A1 through D1?

If as xenou said that you give a sample of your data with expected inputs and results, the solution to this and your other thread -
http://www.mrexcel.com/forum/showthread.php?p=2774750
could be achieved more quickly.

hth

 

[markmzz]

Thanks, I verified that the DATE function behaves as you and MrKowz described it when I use it alone. Excel still can't seem to pick up the pattern when I include it in a larger expression, however. Here is the first equation:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A1)+1,0)))

Here is the 3rd in the sequence:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=A7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:A3),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:A3)+1,0)))

If I manually type in 3 of these and then try to autofill to the 4th, here is what I get in the 4th position:

=SUM((Sheet1!$E3:$E65535)*(Sheet1!$H3:$H65535=$A$2)*(Sheet1!$I3:$I65535=D7)*(Sheet1!$A3:$A65535>=DATE(2011,ROWS($A$1:D1),1))*(Sheet1!$A3:$A65535<=DATE(2011,ROWS($A$1:D1)+1,0)))

As you can see, it autofills to D1 instead of A4 like it should. Is there any way to give Excel a hint as to how to autofill the sequence?

I think this can help:

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH><TH>N</TH><TH>O</TH><TH>P</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">100</TD><TD style="TEXT-ALIGN: center">H</TD><TD style="BACKGROUND-COLOR: #ffff00">Months 2011</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">11</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">12</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">32</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">62</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">2</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">3</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Dpto07</TD><TD style="TEXT-ALIGN: center">I</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">4</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">5</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">6</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">32</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">7</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">8</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">9</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">10</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">11</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">12</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8">62</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD><TD>****</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E3</TH><TD style="TEXT-ALIGN: left">{=SUM((Sheet1!$E$3:$E$43)*(Sheet1!$H$3:$H$43=$A$2)*(Sheet1!$I$3:$I$43=$A$7)*(Sheet1!$A$3:$A$43>=DATE(2011,COLUMNS($A$1:A1),1))*(Sheet1!$A$3:$A$43<=DATE(2011,COLUMNS($A$1:A1)+1,0)))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D4</TH><TD style="TEXT-ALIGN: left">{=SUM((Sheet1!$E$3:$E$43)*(Sheet1!$H$3:$H$43=$A$2)*(Sheet1!$I$3:$I$43=$A$7)*(Sheet1!$A$3:$A$43>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A$3:$A$43<=DATE(2011,ROWS($A$1:A1)+1,0)))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>


Markmzz

 

[Special K]

Hi

You don't mention which cells you are entering these formulas in.

Are they in A1 through A4 or A1 through D1?

If as xenou said that you give a sample of your data with expected inputs and results, the solution to this and your other thread -
http://www.mrexcel.com/forum/showthread.php?p=2774750
could be achieved more quickly.

hth

I was just going to post the spreadsheet as an attachement, but it says attachements aren't allowed. What exactly did you mean by "sample of your data"? Do you want me to type out cell references/values for each page? What would be easiest?