Sumproduct question

[Guest]

I have the following Sumproduct formula and I'm getting a #Value error

=SUMPRODUCT(--(ISNUMBER(32:32)),--(YEAR(32:32)=U16))

I know that A32 is not a number, so the YEAR(32:32) part returns #VALUE for
the first entry. How do I get around this?

Thanks.

 

[Guest]

I don't know what you are trying to accomplish?
32:32 is not the number

 

[Guest]

I'm looking at the array of row 32 to see if the year of the values matches
another predefined year. It's not working when I get the #VALUE error on
the first cell. Apparently anything * #VALUE = #VALUE. Can you help?

 

[Harlan Grove]

Teethless mama wrote...

I don't know what you are trying to accomplish?
32:32 is not the number

It's a range, equivalent to A32:IV32, i.e., the entire 32nd row of the
active worksheet.

You can't use SUMPRODUCT because you need to mask errors. Usually that
means you'd need to use IF to do so, but in this particular case you
could just use COUNT. However, either way that means you need to use an
array formula. Try the array formulas

=COUNT(1/(YEAR(32:32)=U16))

or

=SUM(IF(ISNUMBER(YEAR(32:32)),--(YEAR(32:32)=U16)))

Now if only A2 is nonblank and nonnumeric, you could try pruning it
from your range.

=SUMPRODUCT(--(YEAR(B32:IV32)=U16))