Thread: Sumproduct across several columns

I have the following code in a sheet, it looks at a range of cells and for those containing "READING" in Column BS it looks to Column CJ to see if its value is less than the value 1. The problem is that the word "READING" could be in one of 8 co

=SUMPRODUCT(--($BS$6:$BS$45="READING"),--(CJ$6:CJ$45=""))

The problem is that the word "READING" could be in one of 8 columns. (BS to BZ)

I've tried using the following code but it returns #VALUE! Has anybody got any idea how I can make this work.

=SUMPRODUCT(--($BS$6:$BZ$45="READING"),--(CJ$6:CJ$45=""))

Perhaps:

=SUMPRODUCT(ISNUMBER(MATCH("READING",$BS$6:$BZ$45,0))*(CJ$6:CJ$45=""))

The above assumes that should READING occur more than once per row you wish to count as 1 instance only - if not:

=SUMPRODUCT(($BS$6:$BZ$45="READING")*(CJ$6:CJ$45=""))

The 2nd is in essence an adaptation of your approach - note with Arrays of different dimensions (1x8 & 1x1) you must multiply the Arrays together as opposed to coercing each separately via double unary (#VALUE!)
See the link in my sig. to Bob Phillips' white paper for more info. on SUMPRODUCT

You're not doing anything wrong - the example I gave you won't work - simple as ! Ooops.

=SUMPRODUCT(ISNUMBER(SEARCH("^READING^","^"&$BS$6:$BS$45&"^"&$BT$6:$BT$45&"^"&$BU$6:$BU$45&"^"&$BV$6:$BV$45&"^"&$BU$6:$BU$45&"^"&$BV$6:$BV$45&"^"&$BW$6:$BW$45&"^"&$BX$6:$BX$45&"^"&$BY$6:$BY$45&"^"&$BZ$6:$BZ$45&"^"))*(CJ$6:CJ$45=""))

obviously if you could create a single concatenation string on each row (aping above) then that would simplify the SUMPRODUCT