Formula needed for yes/no calculation

 Blocked Profile -
I am trying to create a formula with an IF function in Excel, but I can't seem to figure it out.<br><br>

Here's the "business case":<br>
I am trying to calculate the amount of spaces that need a specific component installed. <br><br>

There are 3 floors in the building:<br>
Floor 1 has 3 spaces<br>
Floor 2 has 4 spaces<br>
Floor 3 has 2 spaces<br><br>

That's a total of 9 spaces if I want to install a component in all of the spaces. But, I don't. That's why there are also fields with a Yes or No answer. Based on a Yes or No answer for a specific floor I need to calculate the components.<br><br>

The Fields in Excel are as follows:<br>
A2 contains the text "ground floor"<br>
A3 contains the text "1st floor"<br>
A4 contains the text "2nd floor"<br><br>

B2 contains the value "3"<br>
B3 contains the value "4"<br>
B4 contains the value "2"<br><br>

B5 contains the value "9" (=sum(B2:B4))<br><br>

C2 contains the text "yes"<br>
C3 contains the text "no"<br>
C4 contains the text "no"<br><br>

At this point I have figured out how to calculate the following:<br>
If C2 = Yes + C3 = Yes + C4 = Yes<br>
then the formula is: =IF(C2="yes";IF(C3="yes";IF(C4="no";B5)))<br>
the correct result is: 9<br><br>

If C2 = Yes + C3 = Yes + C4 = No<br>
then the formula is: =IF(C2="yes";IF(C3="yes";IF(C4="no";B5-B4)))<br>
the correct result is: 7<br><br>

If C2 = Yes + C3 = No + C4 = No<br>
then the formula is: =IF(C2="yes";IF(C3="no";IF(C4="no";B5-B4-B3)))<br>
the correct result is: 3<br><br>

Here's the question:<br>
I need a formula which combines the above three formula's into one. With this formula I need to be able to get an answer to all possible Yes/No combinations.<br><br>

Can anybody help me?

1 reply


Please, bare in mind that before any answer given on the forum, you should know that there are volunteers who give their time trying to solve user problems. They do have a life other than this forum including me.

Therefore, it is specifically requested from Kioskea forum users to show their respect. For this, the use of polite expressions is a minimum.

To say please, thank you, appreciate, grateful, etc... is common courtesy when you want something, especially help!

You must copy, modify and repost your message respecting the politeness charter.

We trust that you understand.