Using offset in an array function

[RossLarson]

My data is laid out in a large grid (194R X 50C)

E6:BB6 is top row and
E199:BB199 is bottom row.

I want to check if some element is in each row of the grid and create an arrray of 1s and 0s. This is to multiply with another array of 1s and 0s that I already have so I can sum up the result.

However, the followign piece is giving my #VALUE errors when I evaluate the formula

OFFSET(Part1!E5:BB5,ROW(1:194),0,1,50)

this causes the surround match and if statements to return a #N/A error

IF(MATCH(C5,OFFSET(Part1!E5:BB5,ROW(1:194),0,1,50),0) > 0, 1, 0)

Which makes the whole function return #N/A

=SUMPRODUCT(IF(MATCH(C5,OFFSET(Part1!E5:BB5,ROW(1:194),0,1,50),0) > 0, 1, 0), IF(Part1!$B$6:$B$199=1,1,0))

(obviously this is entered with Ctrl + Shift + Enter)

Any ideas on why the offset is erroring out?

Any help is much appreciated.

 

[Marcelo Branco]

Maybe this can work....

=SUMPRODUCT(--(MMULT(--(Part1!$E$5:$BB$199=C5),ROW(Part1!$A$1:$A$50)^0)>0),Part1!$B$5:$B$199)

confirmed with just Enter

M.

 

[Marcelo Branco]

Reviewing your post i noticed that data begin at row 6. So the formula needs a small correction

=SUMPRODUCT(--(MMULT(--(Part1!$E$6:$BB$199=C5),ROW(Part1!$A$1:$A$50)^0)>0),Part1!$B$6:$B$199)

By the way, i think this other formula can also work
=SUMPRODUCT(--(COUNTIF(OFFSET(Part1!$E$6:$BB$6,ROW(Part1!$E$6:$BB$199)-ROW(Part1!$E$6),0,1),C5)>0),Part1!$B$6:$B$199)

M.

 

[RossLarson]

Marcelo,

Thank you very much, that second formula was perfect!

I appreciate your help.

As far as learning goes, can you explain the use of the --?

Thanks again,
Ross

 

[Marcelo Branco]

Marcelo,

Thank you very much, that second formula was perfect!

I appreciate your help.

As far as learning goes, can you explain the use of the --?

Thanks again,
Ross

You are very welcome and thanks for the feedback!

COUNTIF(OFFSET(Part1!$E$6:$BB$6,ROW(Part1!$E$6:$BB$199)-ROW(Part1!$E$6),0,1),C5)>0
returns an array of logical values either True or False, ie, something like
{TRUE;FALSE;FALSE;FALSE;TRUE.......}

-- coerces (converts) True/False to, respectively, 1 or 0 so the SUMPRODUCT can use them in aritmethic operations.

For a very good explanation about SUMPRODUCT take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

M.
ps: BTW, the first formula also worked for me and i think it's better/faster

 

[RossLarson]

Thanks for the explanation of the coercion operator. I will take a closer look at first formula as well.