count and percentages

%3CLINGO-SUB%20id%3D%22lingo-sub-2958401%22%20slang%3D%22en-US%22%3Ecount%20and%20percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958401%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20see%20attached%20example.%20Each%20column%20can%20have%20a%20range%20of%20results%20of%20either%20%22Yes%22%2C%20%22No%22%20or%20%22NA%22.%20Row%2023%20is%20supposed%20to%20give%20the%20total%20%25%20complete%20(ie%20the%20Number%20of%20%22Yes%22%20plus%20the%20number%20of%20%22NA%22%20compared%20to%20the%20total%20number%20of%20rows).%20Whoever%20done%20the%20formula%20has%20simply%20counted%20the%20rows%2C%20multiplied%20by%2010%20and%20then%20formatted%20the%20answer%20as%20a%20%25%2C%20which%20is%20giving%20some%20strange%20results%20(Cell%20F23%20shows%20210%25%3F).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20started%20a%20countifs%20formula%20to%20count%20the%20number%20of%20Yes's%20and%20NA's%2C%20but%20not%20sure%20how%20to%20turn%20the%20result%20into%20a%20%25%20of%20the%20total%20number%20of%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2958401%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2958474%22%20slang%3D%22en-US%22%3ERe%3A%20count%20and%20percentages%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2958474%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1216458%22%20target%3D%22_blank%22%3E%40steelem292%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B23%3A%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(Table1%5BIs%20the%20clinical%20indication%20stated%20in%20the%20patients%20record%3F%5D%2C%22%26lt%3B%26gt%3BNO%22)%2FCOUNTA(Table1%5BIs%20the%20clinical%20indication%20stated%20in%20the%20patients%20record%3F%5D)%3C%2FP%3E%0A%3CP%3EFill%20to%20the%20right.%3C%2FP%3E%0A%3CP%3EThe%20result%20will%20be%20100%25%20in%20all%20columns%2C%20since%20there%20are%20no%20NO%20replies%20in%20your%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Please see attached example. Each column can have a range of results of either "Yes", "No" or "NA". Row 23 is supposed to give the total % complete (ie the Number of "Yes" plus the number of "NA" compared to the total number of rows). Whoever done the formula has simply counted the rows, multiplied by 10 and then formatted the answer as a %, which is giving some strange results (Cell F23 shows 210%?).

 

I started a countifs formula to count the number of Yes's and NA's, but not sure how to turn the result into a % of the total number of rows.

2 Replies

@steelem292 

In B23:

=COUNTIF(Table1[Is the clinical indication stated in the patients record?],"<>NO")/COUNTA(Table1[Is the clinical indication stated in the patients record?])

Fill to the right.

The result will be 100% in all columns, since there are no NO replies in your sample workbook.

thank you @Hans Vogelaar. That has worked a treat.