Trying to get my sumproduct formula to ignore blank cells or 0s

[VGaleoto]

=IF(H7="Long",-IF($D$1="Flat",AVERAGEIFS('3'!$Q$3:$Q$300,'3'!$C$3:$C$300,">="&$D7,'3'!$C$3:$C$300,"<="&$E7),SUMPRODUCT('3'!$Q$3:$Q$300,'3'!$V$3:$V$300)/SUM('3'!$V$3:$V$300)),IF($D$1="Flat",AVERAGEIFS('3'!$Q$3:$Q$300,'3'!$C$3:$C$300,">="&$D7,'3'!$C$3:$C$300,"<="&$E7),SUMPRODUCT('3'!$Q$3:$Q$300,'3'!$V$3:$V$300)/SUM('3'!$V$3:$V$300)))

The above is my formula in excel. It basically calculates the weighted average value of something. I am trying to add a criteria where it will ignore blank cells and 0's. I have tried a few things but nothing is seeming to work for me. Does anyone have any suggestions?

Thank you,

 

[mikerickson]

Unless D7 is negative, the AVERAGEFIS already ignores 0's.
rows where Q3:Q300 or V3:V300 equal 0, contribute nothing to the SUMPRODUCT.

I'm not sure which 0's need ignoring.

 

[VGaleoto]

I should have been more clear, This is part of a pricing model. So, the Q3:Q300 are theoretical values. In some cases they will show up with #VALUE errors. However, most times it is not in the selected data I am wanting to get the weighted average on. However, now that i'm typing it out and thinking about it i'm not sure there is a way to tell sumproduct to only do the calculations on the cells with values (V3:V300) and tell it to use those corresponding cells in Q3:Q300

 

[mikerickson]

Have you looked at AGGREGATE?