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?

 

[xenou]

For sample data, if you take this formula:
=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)))

And reduce the scope:
=SUM((Sheet1!$E3:$E10)*(Sheet1!$H3:$H10=$A$2)*(Sheet1!$I3:$I10=A7)*(Sheet1!$A3:$A10>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A10<=DATE(2011,ROWS($A$1:A1)+1,0)))

And then say what's in E3:E10, H3:H10, I3:I10, and A3:A10 then we could construct a spreadsheet with the same data as yours for an apples-to-apples comparison - i.e., what's good for the goose is good for the gander. This might be as simple as listing the data as text, or using a html-maker such as in markmzz's post. Richard Schollar has a link to his HTMLMaker in his signature. Excel Jeannie is also popular.

 

[Special K]

I'm trying to make a personal finance spreadsheet. Each account (checking, savings, credit card, etc.) will be on a separate worksheet and will have all transactions categorized. On a separate page, I will reference the data across all accounts in order to show expenses by category per month. From there I can create charts.

OK, let me try linking a couple of screenshots. Here is the page that contains the test input data:

https://picasaweb.google.com/lh/photo/SNSZNM5fwMfOOc3KMTcezxH8lz008D7EBBVSOwXODhE?feat=directlink


Here is the summary table that my questions pertain to:

https://picasaweb.google.com/lh/photo/EHMgOBgNAHzTLbWNiW6SCBH8lz008D7EBBVSOwXODhE?feat=directlink


Here is the formula used in cell B7:

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(Sheet1!$A3:$A14>=DATE(2011,ROWS($A$1:A1),1))*(Sheet1!$A3:$A14<=DATE(2011,ROWS($A$1:A1)+1,0)))

The formulas in cells C7:M7 are the ones I am trying to autofill, but the date function isn't autofilling for me. I had to enter all of those formulas manually. For example, the formula in cell M7 should be:

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(Sheet1!$A3:$A14>=DATE(2011,12,1))*(Sheet1!$A3:$A14<=DATE(2011,12,31)))

 

[Special K]

EDIT: Sorry, the formula for cell M7 should be:

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(Sheet1!$A3:$A14>=DATE(2011,ROWS($A$1:A12),1))*(Sheet1!$A3:$A14<=DATE(2011,ROWS($A$1:A12)+1,0)))

I would have edited this in the previous message but it said time had expired for editing.

 

[ukmikeb]

Hi

Try this in B7 -

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(Sheet1!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet1!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

and drag across.

Generally, if you need a value to increase going across the sheet use COLUMNS, going down the sheet use ROWS.

hth

 

[ukmikeb]

Or something like (untested) -

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(TEXT(Sheet1!$A3:$A14,"mmyyyy")=TEXT(B$1,"mmyyyy")))

assuming you have a true date in B1 to M1.

hth

 

[Special K]

Hi

Try this in B7 -

=SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=A7)*(Sheet1!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet1!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

and drag across.

Generally, if you need a value to increase going across the sheet use COLUMNS, going down the sheet use ROWS.

hth

This works perfectly, thanks.

Now for my next question:

I need that array formula to be applied across multiple sheets and summed. This is because I need to be able to calculate income and expenses across multiple accounts. For example, a complete formula for cells B7:M7 would look like this:

=SUM(

SUM((Sheet1!$E3:$E14)*(Sheet1!$H3:$H14=$A$2)*(Sheet1!$I3:$I14=$A7)*(Sheet1!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet1!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0))),

SUM((Sheet2!$E3:$E14)*(Sheet2!$H3:$H14=$A$2)*(Sheet2!$I3:$I14=$A7)*(Sheet2!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet2!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0))),

SUM((Sheet3!$E3:$E14)*(Sheet3!$H3:$H14=$A$2)*(Sheet3!$I3:$I14=$A7)*(Sheet3!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet3!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

)

Where Sheet1, Sheet2, and Sheet3 are separate accounts.

First of all, is that even possible? Can I have a formula that is a sum of array formulas? (UPDATE: Yes, the above formula works just fine as an array formula and gives the correct result)

Second, is there any way to do this that would be easier than copying and pasting in the giant formula above, and then using the autofill to update all other cells? I ask because if I add a new account, that is the only way I would know how to update all of the cells. If I wanted to add a Sheet4, I would need to copy the above formula from one of the cells, copy the Sheet3 line, paste it, change all references to Sheet3 to Sheet4, paste the formula back into the cell, and then use autofill to update all other cells.

Is there any way to just keep a list of the sheet names, and tell Excel to apply the formula across all sheets whose name is contained within that list?

 

[markmzz]

Try this (if is possible the modified in Sheet1, Sheet2, ...):

Master

<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"></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">96</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">186</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"></TD><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">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><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">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center"></TD><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">7</TD><TD style="BACKGROUND-COLOR: #d8d8d8">Dpto07</TD><TD style="TEXT-ALIGN: center">I</TD><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">8</TD><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">9</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">Worksheet 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:Sheet3!E3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Sheet1

<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><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><TH>Q</TH><TH>R</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><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"></TD><TD style="TEXT-ALIGN: right"></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><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><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">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: #bfbfbf">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: #bfbfbf">62</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">24/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">17/09/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">06/05/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">09/04/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">19/06/2011</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"></TD><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">08/07/2011</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</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><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">25/12/2011</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(($V$3:$V$43)*($Y$3:$Y$43=Master!$A$2)*($Z$3:$Z$43=Master!$A$7)*($R$3:$R$43>=DATE(2011,COLUMNS($A$1:A1),1))*($R$3:$R$43<=DATE(2011,COLUMNS($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>

Note: you can hide the columns A:Q in Sheet1, Sheet2, ...

Markmzz

 

[ukmikeb]

Hi

You could follow the suggestion in this thread -
http://help.lockergnome.com/office/Sum-cell-multiple-worksheets--ftopict1016838.html

using which you would not need to keep a log of all the worksheet names.

And your final formula would just be a simple variation of the formula used in each worksheet -

SUM((Start:End!$E3:$E14)*(Start:End!!$H3:$H14=$A$2)*(Start:End!!$I3:$I14=$A7)*(Start:End!!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Start:End!!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

hth

Mike

 

[Special K]

Hi

You could follow the suggestion in this thread -
http://help.lockergnome.com/office/Sum-cell-multiple-worksheets--ftopict1016838.html

using which you would not need to keep a log of all the worksheet names.

And your final formula would just be a simple variation of the formula used in each worksheet -

SUM((Start:End!$E3:$E14)*(Start:End!!$H3:$H14=$A$2)*(Start:End!!$I3:$I14=$A7)*(Start:End!!$A3:$A14>=DATE(2011,COLUMNS($B$1:B1),1))*(Start:End!!$A3:$A14<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

hth

Mike

Thanks for the tip.

 

[Special K]

One more question:

In the formula above, I originally had this:

=SUM(

SUM((Sheet1!$E3:$E65536)*(Sheet1!$H3:$H65536=$A$2)*(Sheet1!$I3:$I65536=$A7)*(Sheet1!$A3:$A65536>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet1!$A3:$A65536<=DATE(2011,COLUMNS($B$1:B1)+1,0))),

SUM((Sheet2!$E3:$E65536)*(Sheet2!$H3:$H65536=$A$2)*(Sheet2!$I3:$I65536=$A7)*(Sheet2!$A3:$A65536>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet2!$A3:$A65536<=DATE(2011,COLUMNS($B$1:B1)+1,0))),

SUM((Sheet3!$E3:$E65536)*(Sheet3!$H3:$H65536=$A$2)*(Sheet3!$I3:$I65536=$A7)*(Sheet3!$A3:$A65536>=DATE(2011,COLUMNS($B$1:B1),1))*(Sheet3!$A3:$A65536<=DATE(2011,COLUMNS($B$1:B1)+1,0)))

)

I had to set all of the references to range from 3:65536 because using something like A:A didn't work. Based on my searching, a reference like A:A doesn't work with array formulas. Is that correct? Given that, would it be faster to hard code the range 3:65536 into all my formulas, or use some type of dynamic range?