Issue with SUMPRODUCT leaving a 0 value in cell

[Brian]

Howdy All,

I have a spreadsheet that contains a couple instances of a formula similar
to this one:

=IF(SUMPRODUCT(--($C$4:C4=C4))>1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

The formula works great, with one exception.

It leaves a 0 value in the cell below the last instance.

This interferes with an AVERAGE formula that I am running on a column.

Any ideas on how to either eliminate the 0 value, or modify my AVERAGE
formula to not count the 0 value?

Thanks,
Brian

 

[Bernard Liengme]

Leave out the cell below "last instance" (what ever that means) when
computing the average?

 

[Brian]

Sorry for the confusion.

But this spreadsheet has info continually added to it.
And in the row below the last entry, the value of that formula displays a 0.
And I average the entire column because the end row is undefined (always
increasing).

 

[Max]

Try something like this, array-entered (press CTRL+SHIFT+ENTER):
=AVERAGE(IF(H4:H501>0,H4:H501))

We can't use entire col references.
If necessary, use something close, like this:
=AVERAGE(IF(H4:H65536>0,H4:H65536))

 

[Aladin Akyurek]

The formula

=IF(SUMPRODUCT(--($C$4:C4=C4))>1,"",SUMPRODUCT(--(C4:$C$501=C4),H4:$H$501))

is too expensive. Invoke instead:

=SUMIF(C4:$C$501,C4,H4:$H$501)

If necessary:

=IF(COUNTIF($C$4:C4,C4)>1,"",SUMIF(C4:$C$501,C4,H4:$H$501))

The average formula can be modified to excluse 0's...

=AVERAGE(IF(Range>0,Range)

confirmed with control+shift+enter.