Conditional SUMPRODUCT to also ignore blanks

[cfdh_edmundo]

Hi,

I have the following code which calculates a weighted average:

=SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203)

It works very well except that I would like it to ignore blank cells in the range $AB$2:$AB$1203

I tried to change it to the following code

=SUMPRODUCT(--($U$2:$U$1203=AE3),$AB$2:$AB$1203,(LEN($AB$2:$AB$1203)>0),$C$2:$C$1203)/SUMIF($U$2:$U$1203,AE3,$C$2:$C$1203)

But this doesn't work and just gives me zero for everything.

If anyone could let me know how I do this that would be great.

Many thanks!

 

[steve the fish]

Re: Help with conditional SUMPRODUCT to also ignore blanks

The double negative in front of this bit:

--(U2:U1203=AE3)

is there to convert a TRUE/FALSE result into a 1 or 0 result. You need the double negative in front of your new TRUE/FALSE test.