|Subject:||Using sumproduct while skipping cells in the array|
|Posted by:||shanen (Shannon.Jacobs.firstname.lastname@example.org)|
|Date:||29 Jan 2007|
I'm trying to use sumproduct in a situation with two arrays of
numbers. It works in the place where the arrays are contiguous,
looking like sumproduct($A7:$A15,C7:C15). However, in one place one of
the arrays is alternating with strings. (This format was decreed from
on high, and I don't want to argue with them about it...) I thought I
should be able to fake it by using sumproduct($A7:$A15>0,C7:C15>0) to
ignore the strings, but Excel refused to buy that. Well, actually
Excel seems to buy it but just returns 0, which is obviously wrong. I
tried a bunch of options trying to select the items I wanted to use,
but there didn't seem to be any way to build an array from elements,
and at that point I might as well just do the sumproduct by hand...
Any other suggestions? A cleaner way to go about it?