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

S

Special K

Board Regular
Joined
Jun 20, 2011
Messages
83
Office Version
  1. 2010
Platform
  1. Windows
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?
 

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.
Sort by date Sort by votes
you could just highlight the range and press fill series instead... that works but i don't know why the autofill doesn't
 
Upvote 0
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
 
Upvote 0
MrKowz said:
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
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
rorya said:
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.
Click to expand...

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?
 
Upvote 0
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).
 
Upvote 0
Special K said:
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?
Click to expand...

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
 
Upvote 0
Special K said:
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?
Click to expand...

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
 
Upvote 0
ukmikeb said:
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
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

WildBurrow
  • Solved
Date Validation Error
Replies
8
Views
523
WildBurrow
WildBurrow
B
  • Solved
Excel2016: Formula won't autofill correctly(Relative/Absolute)
Replies
4
Views
400
Tetra201
Tetra201
B
  • Question Question
Excel 365 Compare Date & Copy Cell Data for Data Base
2 3 4
Replies
33
Views
1K
skillilea
S
Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
337
Jaafar Tribak
Jaafar Tribak
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A

Forum statistics

Threads
1,224,550
Messages
6,179,461
Members
452,915
Latest member
hannnahheileen

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top