Q:

multiple conditions in excel to decide whether sums are valid or invalid

I am building a spread sheet with drop downs and dynamic inputs and need to add multiple conditions with different outputs.  My current formula adds a range of values and outputs to cell C59 depending on "yes" to equal 4; BUT if the last input is "yes" [C59]=4 regardless of previous input.  The formula is as follows:
=IF(C61="Yes", IF(C65="Yes", 4, COUNTIF(C62:C64, "Yes")+1), IF(C65="Yes", 4, 0))
This formula works perfect, but I need to add another condition that would cause the cell to equal 0 regardless of any other condition.  Logically I see this as the addendum:
But, if C18="yes" & C60="no" then C59=0
However I am unable to see the addition in a proper formula format. Maybe I'm just burned out, but any help would be greatly appreciated.

A:

You can just nest another condition:
=IF(AND(C18="Yes", C60="no"), 0, IF(C61="Yes", IF(C65="Yes", 4, COUNTIF(C62:C64, "Yes")+1), IF(C65="Yes", 4, 0)))

Note that Excel don't have an AND operator, you must use AND function or multiplications.
EDIT: Formula simplification:
From this pseudo Karnaugh map:
                  | C61,C65
                  | no,no | no,yes | yes,yes | yes,no
--------+---------+-------+--------+---------+---------
C18,C60 | no,no   | 0     | 4      | 4       | COUNT...
        | no,yes  | 0     | 4      | 4       | COUNT...
        | yes,yes | 0     | 4      | 4       | COUNT...
        | yes,no  | 0     | 0      | 0       | 0

You can optimize your formula to...
... specify outputs once:
=IF(OR(C18<>"Yes", C60="Yes", C16="Yes", C65="Yes"), IF(C65<>"Yes", COUNTIF(C62:C64, "Yes")+1, 4), 0)

... evaluate inputs once:
=IF(OR(C18<>"Yes", C60="Yes"), IF(C65<>"Yes", IF(C61="Yes", COUNTIF(C62:C64, "Yes")+1, 0), 4), 0)

