Formula for calculating Percentages in a list

[dave3944]

I know there has to be a way to do this. Here's the problem: I have a list of items. I would like to calculate the percentage that each item is of the total. For instance, the list of parts includes 48 items consisting of 1's, 2's and 3's. I want to know what percentage of the total are the 1's. What percentage of the total are 2's, etc. I'm very familiar with VBA, but I need this to be a formula. I've experimented with COUNT, COUNTIF, DCOUNT, DCOUNTA and the other functions in Excel. I just know there has to be a way to combine one (or more) of these functions to accomplish the task.
Your help is greatly appreciated.

 

[T. Valko]

I know there has to be a way to do this. Here's the problem: I have a list of items. I would like to calculate the percentage that each item is of the total. For instance, the list of parts includes 48 items consisting of 1's, 2's and 3's. I want to know what percentage of the total are the 1's. What percentage of the total are 2's, etc. I'm very familiar with VBA, but I need this to be a formula. I've experimented with COUNT, COUNTIF, DCOUNT, DCOUNTA and the other functions in Excel. I just know there has to be a way to combine one (or more) of these functions to accomplish the task.
Your help is greatly appreciated.

Something like this...

For the percentage of 1s in the range:

=COUNTIF(A1:A10,1)/COUNT(A1:A10)

Format as Percentage

 

[kidwispa]

I know there has to be a way to do this. Here's the problem: I have a list of items. I would like to calculate the percentage that each item is of the total. For instance, the list of parts includes 48 items consisting of 1's, 2's and 3's. I want to know what percentage of the total are the 1's. What percentage of the total are 2's, etc. I'm very familiar with VBA, but I need this to be a formula. I've experimented with COUNT, COUNTIF, DCOUNT, DCOUNTA and the other functions in Excel. I just know there has to be a way to combine one (or more) of these functions to accomplish the task.
Your help is greatly appreciated.

If your data is in A2:A100, you could use

=COUNTIF($A$2:$A$100,1)/COUNT($A$2:$A$100)

 

[dave3944]

Thanks for the quick answers. I'm halfway there. It works fine with numbers. When I tried it with letters (a,b,c) I get a Division by 0 error. I changed the formula to =COUNTIF(A2:A46,"a")/COUNT(A2:A46), but still got the error. Any suggestions?

 

[T. Valko]

Thanks for the quick answers. I'm halfway there. It works fine with numbers. When I tried it with letters (a,b,c) I get a Division by 0 error. I changed the formula to =COUNTIF(A2:A46,"a")/COUNT(A2:A46), but still got the error. Any suggestions?

If you have both TEXT entries and NUMERIC entries then replace COUNT with COUNTA.

=COUNTIF(A2:A46,"a")/COUNTA(A2:A46)

=COUNTIF(A2:A46,1)/COUNTA(A2:A46)

 

[dave3944]

Perfecto! Works like a charm. Thanks for your time.

 

[T. Valko]

Perfecto! Works like a charm. Thanks for your time.

You're welcome. Thanks for the feedback!