Excel 2016 Complex Nested IF

Question

I'm working with an Excel sheet with more than 2,000 rows. The image I've attached shows the problem. I've checked the questions on StackOverflow & I don't see anything addressing this situation:

I need a formula to calculate the percentage in column E (E is formatted as a percentage). I can do this by hand logically but it takes upwards of 15 minutes and the report needs to be produced daily. The list comes from SQL Server and I can't get my SQL query to handle this either, nor can I make it work in VBA for Excel.

Logic for Cell E3:

If C2='house' and C3='house' and C4='house', then '100'
If C2='house' and C3='house' and C4='prospect', then 'C3/SUM(C3:C4)'
If C2='house' and C3='prospect' and C4='house', then 'C3/SUM(C2:C3)'
If C2='house' and C3='prospect' and C4='prospect', then 'C3/SUM(C2:C3)'
If C2='prospect' and C3='prospect' and C4='prospect', then '100'
If C2='prospect' and C3='house' and C4='house', then, '100'
If C2='prospect' and C3='house' and C4='prospect', then 'C3/SUM(C3:C4)'
If C2='prospect' and C3='prospect' and C4='house', then '100'

Thank you, in advance, for any assistance!

Answer 1

Obviously there was a typo in your question, you meant to calculate with numbers on column D not C, i.e D3/Sum(D3:D4) etc.

You could try to simply write that logic with nested if's... But I realize that the rule, which looked like a digital logic exercise, is actually much simpler. In summary the equation should detect the flip from "House" to "Prospect", if any.

Try this formula at E3 and copy it down:

E3:
=D3/(D3+  (D2*(C2="House")*(C3="Prospect"))  +  (D4*(C3="House")*(C4="Prospect")))