Forum Discussion

Cheyanna_Ward's avatar
Cheyanna_Ward
Copper Contributor
May 02, 2024
Solved

Need help simplifying my nested IF formula

So I have been tracking my progress in transcribing classical music pieces by using nested IF statements to determine whether a multi-part piece is partly or completely done from inputs in the indivi...
excel
Formulas and Functions
  • MAngosto's avatar
    MAngosto
    May 02, 2024

    Cheyanna_Ward 

     

    This may help you:

     

    =IF(COUNTIF(H4:H7,"No")=4,"No",IF(COUNTIF(H4:H7,"Yes")=4,"Yes",IF(COUNTIF(H4:H7,"Partly")>=1,"Partly","")))

     

    It basically counts how many "No", "Partly" and "Yes" are in a set range. If all of the cells in the range are "No", returns "No". If there is at least one "Partly", returns "Partly" and if these are all "Yes, returns "Yes". I don't know if that is exactly the case you needed or you need to locate "Partly" exactly at the last position in the range in order to count the final result as "Partly"... In that case, the formulation will change.

     

    If this suits you, you will only need to change the ranges in each case and also the "4" on the formulation depending on how many movements are in each piece.

MAngosto's avatar
MAngosto
Iron Contributor
May 02, 2024

Cheyanna_Ward 

 

This may help you:

 

=IF(COUNTIF(H4:H7,"No")=4,"No",IF(COUNTIF(H4:H7,"Yes")=4,"Yes",IF(COUNTIF(H4:H7,"Partly")>=1,"Partly","")))

 

It basically counts how many "No", "Partly" and "Yes" are in a set range. If all of the cells in the range are "No", returns "No". If there is at least one "Partly", returns "Partly" and if these are all "Yes, returns "Yes". I don't know if that is exactly the case you needed or you need to locate "Partly" exactly at the last position in the range in order to count the final result as "Partly"... In that case, the formulation will change.

 

If this suits you, you will only need to change the ranges in each case and also the "4" on the formulation depending on how many movements are in each piece.

Resources