 # [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:

...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.