using vlookup (?) to calculate percentages

[davidrias]

I need to write a formula that will do the following to the attached (simplified) worksheet.

The formula needs to:

Count all "N"s in column C that share a row with the "N"s in column A. Then I need to calculate the percentage of "N"s vs "A"s.

For example, I have ten rows with "N" in column A. Nine of these rows have "N" in column C, so my formula would return "90%". One of these rows has an "A", so a second formula will return "10%"

There will be many variations but if I can figure out the formula I can do the rest. I got as far as thinking I need to use vlookup and maybe a count function but have no idea how to set it up. Thanks in advance, someday I will be better and can start helping other people I promise!

[Pepe Le Mokko]

Like

Please Login or Register  to view this content.

perhaps? (same for "n")

[FDibbins]

just FYI, a vlookup does just that...it finds (the 1st instance of) a value that you specify, in a range you define, and returns a value from the column you say....
=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

[davidrias]

no, but I might be able to use a nested COUNTIF? The formula I need will basically be taking a subset of the data. So it will find all rows with "N" in column A, and then the percentages of N vs A is calculated from column C (Only for rows which have "N" in column A)

I will play around with the COUNTIF function though I think that is the right track.

[Pepe Le Mokko]

Have you tried my suggestion?

[davidrias]

Yes Pepe, I am sorry, that is the suggestion I tried. That is only calculating the percentage of all N's and A's in column C which is not what I am looking for.

Check out the example I gave in the OP. Another example would be, since there is one "G" in column A, and the corresponding value in column C is "A", then I would have two formulas that would say that for G, 100% of values are "A" and 0% are "N". The amounts of N's, G's and A's in column A change, thats why I need a formula to first count what is in column A and then evaluate column C for each value (in column A).

Thanks for your help.

[Pepe Le Mokko]

Perhaps something like

Please Login or Register  to view this content.

[davidrias]

I think that works, I tried a couple different scenarios with it and they all seem to give the right answers.

Thanks a bunch! Sorry If I did a bad job explaining what I needed.