Prev: Find a number in Excel file using search
Next: Obtain days in a given year
From: Casey on 14 May 2010 12:03
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
From: Brad on 14 May 2010 12:17
=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
From: Bernard Liengme on 14 May 2010 12:18
=SUMIF(B5:B100, "<>Vacant",D5:D100)
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Casey" <Casey(a)discussions.microsoft.com> wrote in message
news:03FC9D3E-7625-4164-9B52-D025E3F05C92(a)microsoft.com...
> 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

From: Casey on 14 May 2010 14:08
Thank You!


 | 
Pages: 1
Prev: Find a number in Excel file using search
Next: Obtain days in a given year