CODEWITHSUNDEEP
excelexcel-formulasumproduct
Mike
Mike

Reputation: 13

Excel SUMPRODUCT with blank cells

I'm using SUMPRODUCT with COUNTIFS but when there are blank cells it show error. my formula is:

 =SUMPRODUCT((O16:O63="Low")/COUNTIFS(O16:O63,O16:O63,I16:I63,I16:I63))

Upvotes: 1

Views: 950

Answers (4)

bosco_yip
bosco_yip

Reputation: 3802

Try :

=SUMPRODUCT((O2:O16="Low")/COUNTIFS(O2:O16,O2:O16&"",I2:I16,I2:I16&""))

Upvotes: 1

Rory
Rory

Reputation: 34075

You can handle the #DIV/0 errors with IFERROR in an array formula:

=SUMPRODUCT(IFERROR((O16:O63="Low")/COUNTIFS(O16:O63,O16:O63,I16:I63,I16:I63),0))

Upvotes: 0

Justyna MK
Justyna MK

Reputation: 3563

If you have access to Excel 365, then you can try combining COUNT and UNIQUE:

=COUNT(UNIQUE(IF(O16:O23="Low",I16:I23)))

enter image description here

The inner IF formula will return {1;FALSE;3;4;1;FALSE;FALSE;4}. UNIQUE will convert this array into an array of unique values: {1;FALSE;3;4}. COUNT will finally count how many cells in this range contain numbers (3).

Upvotes: 0

Ashgabat
Ashgabat

Reputation: 81

It is because the bold parts in your formula shouldn't be ranges, they must be criteria.

=SUMPRODUCT((O16:O63="Low")/COUNTIFS(O16:O63,O16:O63,I16:I63,I16:I63))

Upvotes: 0

Related Questions