# Calculate percentage, ignoring zeros

#### Gijs

##### New Member
 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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
can you use a sumif() / countif()

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()'?

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

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.

you are welcome

Replies
9
Views
296
Replies
6
Views
656
Replies
6
Views
366
Replies
1
Views
227
Replies
10
Views
695

1,217,317
Messages
6,135,822
Members
449,965
Latest member
Ckl43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back