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

VGaleoto

New Member
Joined
Apr 7, 2020
Messages
11
Office Version
  1. 365
Platform
  1. Windows
=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,
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top