If Statement calculation

[himatvarsani]

I am creating a deal analyser the measures the council requirements for housing and the number of units offered for planning permission based on council requirement (I hope this makes sense). For example, 1 bed 33%, 2 bed 24%, 3 bed 42% - 1 bed I can build 3, 2 bed I can build 2, 3 bed I can build 3 etc....

Also I am trying to create an if statement that if row B11 is commercial do not add to e5 from e11, else deduct D5 from e11, basically, e5 will start with d5 figure and get deducted from e row until close to or equal 0.

I have attached the spreadsheet

 

[richh]

=SUMIF(B11:B20,"<>Commercial",E11:E20) to get non-commercial sqft.

=sum(B11:B20)-SUMIF(B11:B20,"<>Commercial",E11:E20) to get sqft - all non-commercial sqft

 

[himatvarsani]

Hi Richh,

Thank you so much for your response. I had tried your formula, I'm I missing something as it didn't work.

Also From the council requirement (%) how can I calculate the number of units, I don't know if this makes sense.

Thank you
Himat

 

[himatvarsani]

=SUMIF(B11:B20,"<>Commercial",E11:E20) to get non-commercial sqft.

=sum(B11:B20)-SUMIF(B11:B20,"<>Commercial",E11:E20) to get sqft - all non-commercial sqft

Hi Richh,

I have got the formula nearly working, see image below

Formula: =SUM(D5)-SUMIFS(E11:E20,C11:C20,{"Private"},B11:B20,{"Studio Flat","1 Bed Flat"})

When I select Studio Flat the calculation works, but as soon as I select 1 Bed Flat this does work, what have I done wrong with the formula. Other than this, all with the spreadsheet is working correctly.

Thank you
Himat