CODEWITHSUNDEEP
excel
Greens
Greens

Reputation: 3067

Excel SumProduct if Columns Empty

I am trying to calculate a SUMPRODUCT of 3 columns

enter image description here

My formula for a Cell is 

=SUMPRODUCT(((E5:E14)-(I5:I14))*F5:F14)

I need to remove the columns in "I" that have cell values of Empty or Null, The present formula is considering 0 for nulls. How do I accomplish with the formula

Upvotes: 2

Views: 8872

Answers (1)

RocketDonkey
RocketDonkey

Reputation: 37279

One thing you can try is using the following formula:

=SUMPRODUCT(--(I5:I14<>""),E5:E14-I5:I14,F5:F14)

This checks if cells in column I are empty, and if so, it returns 0; otherwise, it returns 1. Your calculation is then run, but anytime an empty I is encountered, your product gets multiplied by 0 and is effectively removed from the sum. Here we separate the arrays by commas - these are what gets multiplied when calculating the product. The -- forces the results of the I5:I14<>"" evaluation to return a 1 or a 0, which is then multiplied through the rest of the values.

Upvotes: 4

Related Questions