Prev: adding result of "if" statement..
Next: mark up
From: UKMAN on 24 Mar 2010 13:58
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
From: Eduardo on 24 Mar 2010 14:09
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
 | 
Pages: 1
Prev: adding result of "if" statement..
Next: mark up