Formula returning value error

**keith6292** · 08-07-2011, 12:19 PM

this is really beating me up so if someone could help me out that would be awesome.
I am using this formula:
SUMPRODUCT(INDIRECT("'BBO Count'!B"&MATCH($A4,'BBO Count'!$B:$B,0)&":J"&MATCH($A4,'BBO Count'!$B:$B,0)),--(MONTH('BBO Count'!$B$3:$J$3)=MONTH(B$3)))
I have added columns to my sheet and when I change the string (highlighted red) it then returns a "Value Error". anyone have any idea why?

**DonkeyOte** · 08-07-2011, 03:00 PM

You would get #VALUE! if

a) any value in 'BBO Count'!B3:J3 was not a valid date (number) as MONTH explicitly coerces (same holds true for B3 on calculation sheet obviously) - this would include formula Null Strings

b) any precedent cells contain underlying #VALUE! errors.

If we assume the values in 'BBO Count'!B3:J3 vary by day (thereby necessitating the month test) and no underlying errors we can remove the coercion issue by using a TEXT construct...

=SUMPRODUCT(--(TEXT('BBO Count'!$B$3:$J$3,"mmm")=TEXT(B$3,"mmm")),INDEX('BBO Count'!$B:$J,MATCH($A4,'BBO Count'!$B:$B,0),0))

If we were only concerned with Null Strings in the Date Header range we could omit the TEXT and use a 0& prefix:

=SUMPRODUCT(--(MONTH(0&'BBO Count'!$B$3:$J$3)=MONTH(B$3)),INDEX('BBO Count'!$B:$J,MATCH($A4,'BBO Count'!$B:$B,0),0))

the TEXT approach is more flexible.

note: INDIRECT is not necessary given INDEX (this also reduces the Volatility of the function)

XL2007+ we would use SUMIFS rather than SUMPRODUCT for this calculation.

**DonkeyOte** · 08-07-2011, 03:10 PM

keith6292,

this question closely resembles that of your other thread

If as implied by the sample on that thread you are indeed using XL2007 or above then you should proceed with SUMIFS in preference to SUMPRODUCT given the formers significant efficiency when compared to the latter. If you require backwards compatibility then obviously SUMIFS is not viable.