Summing several IF's in a column.
Is there an easy way to Sum from a Column within a Logical Operator?
Two Columns. First Either says "Vacant" or says the name of the tenant. Second is the Rent Amount. The Logical Operator is IF (B4<"Vacant",D4,0). But I want to add up the entire column (when Operator is true) within the function. The only way I can think to do it is: =IF(B4<"Vacant",D4,0)+IF(B5<"Vacant",D5,0)+IF(B6 <"Vacant",D6,0) and so forth down the column. At the end the cell should hold the total non-vacant rents for the complex. Any ideas? Thanks, Casey |
Summing several IF's in a column.
=sumproduct(--(B4:B504<"Vacant"),D4:D504))
- to do 500 - the range can be expanded if needed - or =SUMIF(B4:B504<"Vacant",D4:D504) Both work in 2003 and 2007 -- Wag more, bark less "Casey" wrote: Is there an easy way to Sum from a Column within a Logical Operator? Two Columns. First Either says "Vacant" or says the name of the tenant. Second is the Rent Amount. The Logical Operator is IF (B4<"Vacant",D4,0). But I want to add up the entire column (when Operator is true) within the function. The only way I can think to do it is: =IF(B4<"Vacant",D4,0)+IF(B5<"Vacant",D5,0)+IF(B6 <"Vacant",D6,0) and so forth down the column. At the end the cell should hold the total non-vacant rents for the complex. Any ideas? Thanks, Casey |
Summing several IF's in a column.
=SUMIF(B5:B100, "<Vacant",D5:D100)
best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Casey" wrote in message ... Is there an easy way to Sum from a Column within a Logical Operator? Two Columns. First Either says "Vacant" or says the name of the tenant. Second is the Rent Amount. The Logical Operator is IF (B4<"Vacant",D4,0). But I want to add up the entire column (when Operator is true) within the function. The only way I can think to do it is: =IF(B4<"Vacant",D4,0)+IF(B5<"Vacant",D5,0)+IF(B6 <"Vacant",D6,0) and so forth down the column. At the end the cell should hold the total non-vacant rents for the complex. Any ideas? Thanks, Casey |
Summing several IF's in a column.
Thank You!
|
All times are GMT +1. The time now is 01:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com