CODEWITHSUNDEEP
excel-formula
engineer
engineer

Reputation: 51

Excel formula -> how to change SUMPRODUCT formula to skip null cells

context is I am using adjusted sumproduct formula to calculate weighted average.

problem is sumproduct includes empty cells in arrays. => weighted average is calculated incorrectly.

question is How to edit sumproduct to exclude empty cells in arrays?

alternatively Is there another clean and neat solution?

Upvotes: 2

Views: 37998

Answers (1)

John Bustos
John Bustos

Reputation: 19574

In the sumproduct, to exclude empty cells, suppose you're using it on range A1:A100, you could do the following:

= Sumproduct((A1:A100),--(A1:A100<>""))

That second criteria will ensure that you're only looking at cells that have a value in them...

As an explanation (A1:A100<>"") will return an array of True False, where, if there is a value in the cell, it returns true, otherwise, false. Then, including the -- before it, it converts True/False to 1/0. So, in effect, you're multiplying empty cells by a zero (excluding from the formula) and non-empty cells by 1 (including them in the formula).

The --(logical statement for my array) is a very useful trick to use with SumProduct() in MANY different ways!!

Upvotes: 8

Related Questions