Calculate percentage, ignoring zeros

[Gijs]

	m	n
13	90
14	91
15	92
16	92
17	365
18	33	37% =m18/m13
19	0
20	0
21	0
22	33	814%

<tbody>
</tbody>

• 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