Don't calculate range if the word NO is present in a different cell

[richiwatts]

I have this calculation

=SUM(E21:E68,G21:G68,I21:I68)

Is it possible to exclude any of the columns from the calculation is No is present?

If E20 says No the my calculation would be =SUM(G21:G68,I21:I68)
If G20 says No the my calculation would be =SUM(E21:E68,I21:I68)

If E20 and G20 says No the my calculation would be =SUM(I21:I68)

..and so on

Rich

 

[FDibbins]

Im sure there is a more complex way to do this, but if you only have 3 columns, why not break them apart with something like this...
=if(E20="No",0,SUM(E21:E68))+if(G20="No",0,SUM(G21:G68))+if(I20="no,0,sum(I21:I68))

How many columns do you have anyway?

 

[AhoyNC]

Probably a better way, but:

=SUM(IF(E20<>"No",E21:E68,0))+SUM(IF(G20<>"No",G21:G68),0)+SUM(IF(I20<>"No",I21:I68,0))

 

[FDibbins]

Ahoy, there must be a weird echo in here LOL

 

[richiwatts]

Thank you both!!!!!! Ahoy's worked. Don't need a better way when I get the results I had hoped for.

Rich

 

[AhoyNC]

You're welcome. Ford's formula should also work, he just left off a double quote at the end of the last "no".

=IF(E20="No",0,SUM(E21:E68))+IF(G20="No",0,SUM(G21:G68))+IF(I20=[COLOR=#ff0000]"no"[/COLOR],0,SUM(I21:I68))

Thanks for the feedback.

 

[FDibbins]

You're welcome. Ford's formula should also work, he just left off a double quote at the end of the last "no".

=IF(E20="No",0,SUM(E21:E68))+IF(G20="No",0,SUM(G21:G68))+IF(I20=[COLOR=#ff0000]"no"[/COLOR],0,SUM(I21:I68))

Thanks for the feedback.

Thanks for the assist