Finding % based on multiple cells and using MATCH formula

Question

Trying to come up with a formula for a spreadsheet I am working on, but hit a road block.

I need to use an INDEX and MATCH formula to ensure that the cell is going to lookup a sheet full of data and find all matches based on an ID number. I then need to work out the % value of how often that column was populated with data for that ID number.

Essentially as an example:

The data table would look like:

ID | Notes Added  
1  | Yes 
7  | Yes 
12 | Yes 
6  | Yes 
10 |  
12 |

And the result would be:

ID | Populated %
12 | 50 %

As you can see, the values that it should return would be 50%, as there are 2 entries that are listed for the ID of 12 (this is where the MATCH and INDEX would come in), however in the Notes Added, only 1 of the entries has a value.

The data table that it draws from will either have a value, or have no value, calculating the % would be based on if there is/isn't a value.

TyloBedo TyloBedo · Accepted Answer · 2016-11-15T04:00:47

You'll want to use COUNTIFS Function. It would look something like this:

=COUNTIFS(B4:B9,C16,C4:C9,"Yes") / COUNTIF(B4:B9,C16)

=COUNTIFS(<ID Values Range>,<Id Value>,<notes added range>,"Yes") / COUNTIF(<ID Values Range>,<Id Value>)

Then just set the cell formatting to %.

Finding % based on multiple cells and using MATCH formula

1 Answers