Function to add 3 column (of YES) to give a total % complete

[preyesone]

I am trying to write a formula that will allow me to add a Yes to one cell
(in 3 different columns) cell and then give a % as the answer
For example:

Cell A1 = Yes Total would be 40%
Cell A2 = Yes Combined (A1+A2) Total would be 70%
Cell A3 = Yes Combined (A1+A2+A3) Total would be 100%

Any and all help is appreciated!

 

[Eduardo]

Hi,
try

=IF(AND(a1="YES",a2="YES",a3="YES"),"100%",IF(AND(a1="YES",a2="YES"),"70%",IF(a1="YES","40%","")))

 

[preyesone]

Thank you... Thank you... Thank you!

Hi,
try

=IF(AND(a1="YES",a2="YES",a3="YES"),"100%",IF(AND(a1="YES",a2="YES"),"70%",IF(a1="YES","40%","")))

 

[Teethless mama]

=(A1="yes")*0.4+((A2="yes")+(A3="yes"))*0.3

 

[Teethless mama]

This one slightly shorter.

=(A1="yes")*0.4+COUNTIF(A2:A3,"yes")*0.3

 

[Ken]

A little different approach:

=INDEX({0;40;70;100},COUNTIF(D15:F15,"Yes")+1)

This assumes your "yeses" go in columns D, E, and F and that you want
a 0 if there are none.

Good luck

Ken
Norfolk, Va

 

[Eduardo]

your welcome

Thank you... Thank you... Thank you!

 

[Shane Devenshire]

Hi,

here are two short approaches

Array enter
=SUM((A1:A3="yes")*{0.4;0.3;0.3})

to array enter this press Shift+Ctr+Enter

or create a range contain 40% 30% and 30%, in the example below B1:B3

=SUMIF(A1:A3,"yes",B1:B3)