YMTEO
Mon May 12 04:58:01 CDT 2008
Hi,
I have filled up all the blank and tried the formular.
But it is still not working.
However, I have tried the formular below
=SUM(IF($D$5:$D$225="NK",IF($S$5:$S$225="LF",I$5:I$225,0),0)
and it works OK fine in suming up sales for one sales person.
If I tried to add another sales person by amending the formular to
=SUM(IF($D$5:$D$225=("NK","SG"),IF($S$5:$S$225="LF",I$5:I$225,0),0), I got
#N/A.
Is there any other formulars?
"Max" wrote:
> Assuming the data posted is within A1:F22, with the salesman col (col A)
> fully populated from above** (this is required, see below for link to get
> this done on a copy of the pivot. Alternatively, you could frame it up to
> directly read the source table for the pivot)
>
> you could place this in say, H1:
> =SUMPRODUCT((ISNUMBER(MATCH($A$2:$A$22,{"SG","NK"},0)))*($F$2:$F$22="NLF")*$C$2:$E$22)
> which returns the total sales of Product NLF, made by Salesman SG and NK for
> the months of Jan, Feb and Mar (total for the 3 months)
>
> If you need separate totals by the month for Jan, Feb and Mar
> place this in say, H2:
> =SUMPRODUCT((ISNUMBER(MATCH($A$2:$A$22,{"SG","NK"},0)))*($F$2:$F$22="NLF"),C$2:C$22)
> then copy H2 across to J2, to return desired results
>
> **so that it looks like this
> Salesman
> SG
> SG
> SG Total
> JD
> JD
> ...
> JD Total
> NK
> ...
> NK
> NK Total
>
> Try Debra Dalgleish's page for ways to fill in the col blanks:
>
http://www.contextures.com/xlDataEntry02.html
> Excel -- Data Entry -- Fill Blank Cells
>
> Fill Blank Cells
> Fill Blank Cells Programmatically
> (Sub FillColBlanks() by Dave Peterson)
> --
> Max
> Singapore
>
http://savefile.com/projects/236895
> xdemechanik
> ---
> "YMTEO" wrote:
> > Hi Max,
> >
> > Assume the data format below is similar to a Pivot table, with column and
> > sub total
> >
> > What should I do, if I want to the total sales of Product NLF, made by
> > Salesman SG and NK for the month of Jan, Feb and Mar?
> >
> > Salesman Product Jan Feb Mar Prdt Group
> > SG Pdt A NLF
> > Pdt B LF
> > SG Total
> > JD Pdt A NLF
> > Pdt C LF
> > Pdt D NLF
> > Pdt E LF
> > Pdt G NLF
> > Pdt K NLF
> > Pdt L LF
> > Pdt M LF
> > JD Total
> > NK Pdt A NLF
> > Pdt B LF
> > Pdt C LF
> > Pdt D NLF
> > Pdt E LF
> > Pdt F LF
> > Pdt G NLF
> > Pdt M LF
> > NK Total
> >
> >
> > "Max" wrote:
> >
> > > As a first attempt, you could try posting your formula and describe your
> > > set-up & intents in plain text.
> > >
> > > Trying "multiple condition in SUMIF" usually points to SUMPRODUCT,
> > > Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"),C1:C10)
> > > will calc the sum of numbers in C1:C10 for rows where
> > > A1:A10 contains the number: 111,
> > > and
> > > B1:B10 contains the text: "xxx"
> > > --
> > > Max
> > > Singapore
> > >
http://savefile.com/projects/236895
> > > xdemechanik
> > > ---
> > > "YMTEO" <YMTEO@discussions.microsoft.com> wrote in message
> > > news:89362DD7-614A-474D-B4A8-D530CBA48FE7@microsoft.com...
> > > > Hi all,
> > > >
> > > > I have read most of the notes and e-mail from the community section.
> > > > However, I still failed to get the answer I want, after trying various method.
> > > >
> > > > Is there a way where I can attached my worksheet and show my problem to
> > > > anysone?
> > > >
> > >