Need a SumIf (SumProduct?) that Ignors Zeros

[bluerog]

I am putting together average sell price weighted by quarter and year. My "sell prices" have $0 (and negatives) for some quarters.

I'm looking for a sumifs or sumproduct formula that will ignore zeros.

For instance if I'm weighting 10% 2013, 30% 2014, and 60% 2015 for $25, $35, $24 the formula is (25*10% + 35*30% + 24*60%) and I get a nice weighted average.

But if there's a $0, it throws the formula off and the weighted average is bad.


See attached (note: Much more data in real worksheet). And thank you in advance.

[oeldere]

what should be the result if of the values is 0?

[Pete_UK]

So, if a 60% (2015) is missing, as in your highlighted cells, do you want 2013 to be 25% and 2014 to be 75% ? And if 2013 and/or 2014 is missing, you want those %ages to be re-distributed ?

Pete

[oeldere]

See if the result in the tab pivot table. is the result is what you expect.

See the attached file.