adding figures that match 2 true and 1 false if statement conditio

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

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" wrote in message
...
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

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

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" wrote in message
...
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" wrote in message
...
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

Eduardo,

if only I could get a course I would understand some of your code but I
have been able to amend it to meet my needs so your are brill

many thanks

UKMAN

"Eduardo" wrote:

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

Hi,
take a look at this web it has good explanations and examples using sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

thanks for the feedback

"UKMAN" wrote:

Eduardo,

if only I could get a course I would understand some of your code but I
have been able to amend it to meet my needs so your are brill

many thanks

UKMAN

"Eduardo" wrote:

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