Using sumproduct while skipping cells in the array

[shanen]

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?

 

[Dave Peterson]

=sumproduct($A7:$A15,C7:C15)

Will treat text values as 0's. So they won't add to the sum.

I don't understand the problem.

 

[shanen]

Thanks for the surprisingly prompt reply. However...

Alas and alack, one would think so, but it actually returns the $VALUE
error. Strangely enough, if the interleaved cells are blank, the
formula evaluation seems to show them evaluating to 0, which would be
good enough, but in actuality some of them are per force not blank.

Forgot to mention that I thought the problem might involve the
mysterious use of <Ctrl><Shift><Enter>, but I wasn't able to get any
joy from that approach, either. I wound up just hard-coding the cells
in that part of the spreadsheet, but sumproduct would have been a much
more elegant solution... I'm still curious where I went wrong.

 

[Dave Peterson]

Check your formula once more. Are you sure that the ranges are equal size. I'm
guessing that your post was not the real formula that you're using.

Do you have any hidden cells in either of those ranges? Do those hidden cells
contain errors?

 

[Roger Govier]

Hi

As Dave said, text values in either of the arrays do not cause the
Sumproduct formula to fail.
Can you list out what the 16 values are you have in the two ranges?

--
Regards

Roger Govier

Thanks for the surprisingly prompt reply. However...

Alas and alack, one would think so, but it actually returns the $VALUE
error. Strangely enough, if the interleaved cells are blank, the
formula evaluation seems to show them evaluating to 0, which would be
good enough, but in actuality some of them are per force not blank.

Forgot to mention that I thought the problem might involve the
mysterious use of <Ctrl><Shift><Enter>, but I wasn't able to get any
joy from that approach, either. I wound up just hard-coding the cells
in that part of the spreadsheet, but sumproduct would have been a much
more elegant solution... I'm still curious where I went wrong.