Problem with multiple condition in SUMIF

Y

YMTEO

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?
 
M

Max

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"
 
Y

YMTEO

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
 
M

Max

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)
 
Y

YMTEO

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?
 
M

Max

You need to clear all the #N/A data in col R. This is what is causing the
problem for you. To clear all at one go, just do an autofilter on col R,
select: #N/A, then select all the filtered rows in col R, press Delete to
clear. Remove the filter then try placing in say, K2
=SUMPRODUCT((ISNUMBER(MATCH($D$6:$D$70,{"SG","NK"},0)))*($R$6:$R$70="F-WaterSol")*$H$6:$P$70)
which should now return the correct results
(I've entered some dummy data here and there within the source to illustrate)

Here's your sample to show the above:
http://www.freefilehosting.net/download/3h61d
case_ymteo.xls

Take a moment to press the "Yes" button below ..
 
Top