# [Solved] SumProduct Query

• I have a spreadsheet for recording hours spent on various project. I want to break this down into months so I have the sum of the hours for each month. The only problem I have is that I can find the sum of hours, however there are fields in the range that contain H's indicating holiday. Anyone know how I can adjust the formula below to ignore Text values. At the moment it is giving me a #VALUE! error...

TimeTracker is the Sheet Name, the MATCH looks up the project name and References are worked off dates...

=SUMPRODUCT((MONTH(J\$3)=MONTH(TimeTracker!\$B\$2:\$HG\$2))*(OFFSET(TimeTracker!\$A\$1,MATCH(\$B4,TimeTracker!\$A:\$A,0)-1,1,1,214)))

Any help would be great...

• The following worked for me on a simplke example. You need to surround the Offset part with an If statement to check for numeric values. It will make a long function and it will need to be entered as an array function (control shift enter), For my simple case the formula was
=SUMPRODUCT((A2:A6=A9)*(IF(ISNUMBER(B2:B6),B2:B6,0)))

• see the example:

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"&gt;</SCRIPT&gt;<CENTER&gt;<TABLE cellSpacing=0 cellPadding=0 align=center&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6&gt;<TABLE width="100%" align=center border=0&gt;<TBODY&gt;<TR&gt;<TD align=left&gt;<FONT color=white&gt;Microsoft Excel - Book6</FONT&gt;</TD&gt;<TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right&gt;___Running: xl2000 : OS = Windows Windows 2000 </FONT&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6&gt;<TABLE width="100%" align=center border=0 VALIGN="MIDDLE"&gt;<TBODY&gt;<TR&gt;<TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption"&gt;<U&gt;F</U&gt;ile <U&gt;E</U&gt;dit <U&gt;V</U&gt;iew <U&gt;I</U&gt;nsert <U&gt;O</U&gt;ptions <U&gt;T</U&gt;ools <U&gt;D</U&gt;ata <U&gt;W</U&gt;indow <U&gt;H</U&gt;elp <A onclick=show_popup(); href="#javascript:void(0)"&gt;<U&gt;A</U&gt;bout</A&gt;</TD&gt;<TD vAlign=center align=right&gt;<FORM name=formCb118219&gt;<INPUT onclick='window.clipboardData.setData("Text",document.formFb130085.sltNb782009.value);' type=button value="Copy Formula" name=btCb047069&gt;</FORM&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6&gt;<TABLE border=0&gt;<TBODY&gt;<TR&gt;<FORM name=formFb130085&gt;<TD style="WIDTH: 60px" align=middle bgColor=white&gt;<SELECT onchange="document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value" name=sltNb782009&gt;<OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6)) selected&gt;E2<OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1)),(B2:B6))&gt;E3<OPTION value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))+0,(B2:B6))&gt;E4</OPTION&gt;</SELECT&gt;</TD&gt;<TD align=right width="3%" bgColor=#d4d0c8&gt;<B>=</B&gt;</TD&gt;<TD align=left bgColor=white&gt;<INPUT size=80 value==SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6)) name=txbFb216056&gt;</TD&gt;</FORM&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<BR&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;A</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;B</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;C</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;D</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle&gt;<CENTER&gt;E</CENTER&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;1</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Date</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Hours</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Month</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/01/2002</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;2</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/01/2002</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;5</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Total&nbsp;1</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;<A onclick="document.formFb130085.sltNb782009.options[0].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);"&gt;#VALUE!</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;3</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/01/2002</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;5</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Total&nbsp;2</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;<A onclick="document.formFb130085.sltNb782009.options[1].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);"&gt;0</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;4</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/01/2002</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;H</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;Total&nbsp;3</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;<A onclick="document.formFb130085.sltNb782009.options[2].selected=true; document.formFb130085.txbFb216056.value = document.formFb130085.sltNb782009.value;" href="#javascript:void(0);"&gt;15</FONT&gt;</A&gt;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;5</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/01/2002</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;5</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"&gt;<CENTER&gt;6</CENTER&gt;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;1/02/2002</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;5</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: #000000; BORDER-BOTTOM: #d4d0c8 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff"&gt;&nbsp;</TD&gt;</TR&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" colSpan=6&gt;<TABLE width="100%" align=left VALIGN="TOP"&gt;<TBODY&gt;<TR&gt;<TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #808080 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; WIDTH: 120pt; BORDER-BOTTOM: #000000 0.5pt solid; BACKGROUND-COLOR: #ffffff" align=left&gt;<U&gt;Sheet1</U&gt;</TD&gt;<TD&gt;&nbsp;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;</TD&gt;</TR&gt;</TBODY&gt;</TABLE&gt;<BR&gt;<FONT color=#339966 size=1&gt;[HtmlMaker light Ver1.10] </FONT&gt;<FONT color=#339966 size=1&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT color=red size=1&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</FONT&gt;</CENTER&gt;

...which includes 3 sumproduct() formulas as examples.

Total 1:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))*(B2:B6))

attempts to multiply directly a series of true / false returns with a series of numbers. because there is a text value in the number range, you get the value error - examination of formula components shows that there's only a single array involved - i.e. the results of the multiplication - and that the array fed into the sumproduct is:

{5;5;#value!;5;0}

...hence the error.

Total 2:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1)),(B2:B6))

Rather than doing the array multiplication directly, this version returns 2 arrays that are then fed into the sumproduct. Investigation shows the arrays to be:

{TRUE;TRUE;TRUE;TRUE;FALSE}
and
{5;5;"H";5;5}

however, the formula returns 0 because sumproduct will not treat the logicals as 1 + 0...

so:

Total 3:

=SUMPRODUCT((MONTH(A2:A6)=MONTH(E1))+0,(B2:B6))

uses the +0 to coerce the logicals into numbers (1 & 0), which are then fed into the sumproduct to return the expected results. the arrays in this case are:

{1;1;1;1;0}
and
{5;5;"h";5;5}

...which gives us the result we need.

So - the moral is don't do the array multiplications directly if there are likely to be text values in numeric ranges.