CODEWITHSUNDEEP
excelexcel-formula
MA84
MA84

Reputation: 107

Can i use SUMPRODUCT to ignore blank cells?

My spreadsheet has a list of names (some are repeated) in column A, a list of numbers stored in a string in column B, and column C uses a formula to get the first number of the string in column B. In column E have created a list of the unique names from column A, in column F a number of times they appear in the data list and in column G i then want to fetch the corresponding number data from column C each time it appears in the list to calculate average numbers.

I have tried this

=SUMPRODUCT(($A$1:INDEX($A:$A,COUNTA($A:$A))=$E4)*($C$2:INDEX($C:$C,COUNTA($C:$C))))/$F4

The problem i have is that in the list of data some of the cells in column C are blank so i am getting a #VALUE error.

Here is a screenshot of what i am trying:

new output

Is there anyway to tell SUMPRODUCT to skip the rows where there is no number data?

Obviously this is just an example and my actual spreadsheet is a little more complicated, there are thousands of rows of data and the names are repeated many times over.

Upvotes: 0

Views: 1761

Answers (2)

JvdV
JvdV

Reputation: 75990

Empty cells are not your problem. It would just be accepted in a formula like yours. Unfortunately the problem is because you have gaps, COUNTA will return a range that's not equal to column A > COUNTA in column A will return 15, whereas COUNTA in column C will return 11. Unequal ranges will return #VALUE

In this specific case your issue is resolved through:

=SUMPRODUCT(($A$1:INDEX($A:$A,COUNTA($A:$A))=$E4)*($C$1:INDEX($C:$C,COUNTA($A:$A))))/$F4

enter image description here

Upvotes: 1

bosco_yip
bosco_yip

Reputation: 3802

In G4, copied down :

=SUMIF($A:$A,$E4,$C:$C)/$F4

Edit : SUMIF() can use whole column reference of which bounded on used range only, and can avoid to use dynamic range.

Upvotes: 1

Related Questions