Prev: Excel not pasting the formula or reference
Next: Date Formatting
From: UKMAN on 24 Mar 2010 12:43
As ever many thanks in advance for any help.

I am struggling to understand how I can say not equal in an if statement in
excel:(

I need to consildate a table which layout is basically:

Col A Col b Col c Col d Col e
(name Car basic Bonus status)

sid 50 100 10 On hold
mary 20 50 5 approved
sid 40 90 10 rejected
sid 30 35 10 review

As this is to do with a budget the consolidation will use the same headings
but the status I want to exclude from the calc for each col is "rejected".

therefore the summary field need to say if Col a = sid, if col b = Car, if
col e not equal "Rejected" then the total car costs for sid is 80. (hope
that makes sence :)

As i mentioned this is a cutdown version of a large sheet.
Cheers UKMAN
From: Luke M on 24 Mar 2010 13:18
Perhaps something like this:

=SUMPRODUCT((A2:A10="sid")*(B2:B10="car")*(E2:E10<>"Rejected")*(C2:C10))
explained:
=SUMPRODUCT(LogicCheck1 * LogicCheck2 * LogicCheck3 * ValuesToSum)

You're example was a little unclear, as it doesn't clearly say how the 80 is
calculated, and column B has values, not text. Hopefully you can adapt this
formula's pattern to suit.

--
Best Regards,

Luke M
"UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message
news:2B94CF04-A7EB-4485-9C25-6AA8E78B8ECF(a)microsoft.com...
> As ever many thanks in advance for any help.
>
> I am struggling to understand how I can say not equal in an if statement
> in
> excel:(
>
> I need to consildate a table which layout is basically:
>
> Col A Col b Col c Col d Col e
> (name Car basic Bonus status)
>
> sid 50 100 10 On hold
> mary 20 50 5 approved
> sid 40 90 10 rejected
> sid 30 35 10 review
>
> As this is to do with a budget the consolidation will use the same
> headings
> but the status I want to exclude from the calc for each col is
> "rejected".
>
> therefore the summary field need to say if Col a = sid, if col b = Car, if
> col e not equal "Rejected" then the total car costs for sid is 80. (hope
> that makes sence :)
>
> As i mentioned this is a cutdown version of a large sheet.
> Cheers UKMAN


From: Eduardo on 24 Mar 2010 13:20
Hi,
As this is a consolidation I assume you have in column A name (sid in A2),
in column B car in B2, in column c you want the total. I assume that the
information is in sheet called "Data", change it and range to fit your needs.

=sumproduct(--(A1=Data!$A$2:$A$1000),--(Data!$E$2:$E$1000<>"Rejected"),Data!$$B$2:$B$1000)


"UKMAN" wrote:

> As ever many thanks in advance for any help.
>
> I am struggling to understand how I can say not equal in an if statement in
> excel:(
>
> I need to consildate a table which layout is basically:
>
> Col A Col b Col c Col d Col e
> (name Car basic Bonus status)
>
> sid 50 100 10 On hold
> mary 20 50 5 approved
> sid 40 90 10 rejected
> sid 30 35 10 review
>
> As this is to do with a budget the consolidation will use the same headings
> but the status I want to exclude from the calc for each col is "rejected".
>
> therefore the summary field need to say if Col a = sid, if col b = Car, if
> col e not equal "Rejected" then the total car costs for sid is 80. (hope
> that makes sence :)
>
> As i mentioned this is a cutdown version of a large sheet.
> Cheers UKMAN
From: Luke M on 24 Mar 2010 13:25
Also, since you have a nice table format, you could prb create the summary
table via PivotTable. This would save on calc time and flexibility.

--
Best Regards,

Luke M
"Luke M" <lukemoraga(a)nospam.com> wrote in message
news:%23qM07X3yKHA.4156(a)TK2MSFTNGP06.phx.gbl...
> Perhaps something like this:
>
> =SUMPRODUCT((A2:A10="sid")*(B2:B10="car")*(E2:E10<>"Rejected")*(C2:C10))
> explained:
> =SUMPRODUCT(LogicCheck1 * LogicCheck2 * LogicCheck3 * ValuesToSum)
>
> You're example was a little unclear, as it doesn't clearly say how the 80
> is calculated, and column B has values, not text. Hopefully you can adapt
> this formula's pattern to suit.
>
> --
> Best Regards,
>
> Luke M
> "UKMAN" <UKMAN(a)discussions.microsoft.com> wrote in message
> news:2B94CF04-A7EB-4485-9C25-6AA8E78B8ECF(a)microsoft.com...
>> As ever many thanks in advance for any help.
>>
>> I am struggling to understand how I can say not equal in an if statement
>> in
>> excel:(
>>
>> I need to consildate a table which layout is basically:
>>
>> Col A Col b Col c Col d Col e
>> (name Car basic Bonus status)
>>
>> sid 50 100 10 On hold
>> mary 20 50 5 approved
>> sid 40 90 10 rejected
>> sid 30 35 10 review
>>
>> As this is to do with a budget the consolidation will use the same
>> headings
>> but the status I want to exclude from the calc for each col is
>> "rejected".
>>
>> therefore the summary field need to say if Col a = sid, if col b = Car,
>> if
>> col e not equal "Rejected" then the total car costs for sid is 80. (hope
>> that makes sence :)
>>
>> As i mentioned this is a cutdown version of a large sheet.
>> Cheers UKMAN
>
>


 | 
Pages: 1
Prev: Excel not pasting the formula or reference
Next: Date Formatting