SUMPRODUCT Across Multiple Sheets returns #NAME error in 2010

[drewcarpenter]

I have an invoicing/accounting system for my home-based business that sums $ amounts billed across 30+ sheets for individual customers, based upon dates. The formula I currently have is as follows:
=SUMPRODUCT((THREED(First:Last!B$1:B$300)=A2)+0,(THREED(First:Last!F$1:F$300)))

In the formula, all my sheets are bracketed by sheets name "First" and "Last", column B is the date array shown below (Invoice Date column), which is contained in each individual invoice. A2 is the date reference in my total revenue tab (adjacent to the #NAME error below). Column F is the $ Billed column in the invoices...

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=149><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl209 height=21 width=68></TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl211 width=81>Billed</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl210 height=21 width=68>1/1/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0" class=xl212>#NAME?</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=126><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4e8; WIDTH: 95pt; HEIGHT: 30.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl212 height=41 width=126>Invoice Date</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>1/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>2/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>3/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>4/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>5/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>6/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>7/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>8/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>9/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>10/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>11/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>12/1/2011</TD></TR></TBODY></TABLE>

This formula works beautifully in 2007, but 2010 doesn't seem to recognize it. Any suggestions for translating it to 2010, or for creating a different formula which might solve my predicament?

 

[Jonmo1]

Make sure the Morefunc Addin is properly installed

 

[Aladin Akyurek]

I have an invoicing/accounting system for my home-based business that sums $ amounts billed across 30+ sheets for individual customers, based upon dates. The formula I currently have is as follows:
=SUMPRODUCT((THREED(First:Last!B$1:B$300)=A2)+0,(THREED(First:Last!F$1:F$300)))

In the formula, all my sheets are bracketed by sheets name "First" and "Last", column B is the date array shown below (Invoice Date column), which is contained in each individual invoice. A2 is the date reference in my total revenue tab (adjacent to the #NAME error below). Column F is the $ Billed column in the invoices...

<TABLE style="WIDTH: 112pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=149><COLGROUP><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl209 height=21 width=68></TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl211 width=81>Billed</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 51pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl210 height=21 width=68>1/1/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0 0.5pt; BORDER-RIGHT: #f0f0f0" class=xl212>#NAME?</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=126><COLGROUP><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 30.75pt" height=41><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8e4e8; WIDTH: 95pt; HEIGHT: 30.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl212 height=41 width=126>Invoice Date</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>1/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>2/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>3/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>4/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>5/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>6/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>7/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>8/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>9/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #969696 0.5pt solid" class=xl210 height=26 width=126>10/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>11/1/2011</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: #969696 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 95pt; HEIGHT: 19.5pt; BORDER-TOP: #969696; BORDER-RIGHT: #969696 0.5pt solid" class=xl211 height=26 width=126>12/1/2011</TD></TR></TBODY></TABLE>

This formula works beautifully in 2007, but 2010 doesn't seem to recognize it. Any suggestions for translating it to 2010, or for creating a different formula which might solve my predicament?

If your 2010 system is 64bit, the current version of morefunc won't work.

An option is to create a range housing all of the relevant sheet names and naming that range as e.g. SheetList, then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!B1:B300"),A2,INDIRECT("'"&SheetList&"'!F1:F300))

See also

http://www.mrexcel.com/forum/showthread.php?t=119020

for code (by Jindon) which collects the sheet names.