Using sumproduct while skipping cells in the array

Giganews Newsgroups
Subject: Using sumproduct while skipping cells in the array
Posted by:  shanen (Shannon.Jacobs.nospam@gmail.com)
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?

Replies