Calculate percentage, ignoring zeros

[Gijs]

[TABLE="width: 100"]
<tbody>[TR]
[TD="align: right"]
[/TD]
[TD="align: right"]m
[/TD]
[TD="align: right"]n
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]14
[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]15
[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]16
[/TD]
[TD="align: right"]92
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]365
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]18
[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]37%
=m18/m13[/TD]
[/TR]
[TR]
[TD="align: right"]19
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]20
[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]21
[/TD]
[TD="align: right"]0
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]814%
[/TD]
[/TR]
</tbody>[/TABLE]

• n22 should be 37%
• n22: 9% -- includes zeros, delivers a wrong percentage
  =m22/m17
• n22: 814% -- Trying to avoid zeros, delivers a wrong percentage.
  =SUMPRODUCT(m18:m21,m13:m16)/SUMPRODUCT(--(m18:m21<>""),m13:m16)

Does anyone have an idea how so solve this?

 

[etaf]

can you use a sumif() / countif()

 

[Gijs]

Thanks etaf for your fast response on this sunny Sunday.
Regrettably your advice 'a sumif() / countif()' is too cryptic for me. I tried =SUMIF(M19:M22,">0")/COUNTIF(M14:M17,">0") which evidently doesn't work. Can you clarify the usage of 'sumif() / countif()'?

 

[etaf]

try
=SUMIF(M19:M22,"<>"&0)/COUNTIF(M14:M17,"<>"&0)

 

[Gijs]

Thanks again etaf, especially your efforts to solve my question on a sunny Sunday.

Mulling over the challenge, I realized I wasn't clear in posting the question. What I needed is a weighted percentage, which I've meanwhile resolved with a somewhat convoluted formula:

=IF(N22>"",SUM(M19:M22)/SUM(M14:M17),
IF(N21>"",SUM(M19:M21)/SUM(M14:M16),
IF(N20>"",SUM(M19:M20)/SUM(M14:M15),N19)))
​

Thanks again for your fast response. Have a great week ahead.

 

[etaf]

you are welcome