 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008Problem with multiple condition in SUMIF
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? 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"  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi all, > [quoted text clipped - 3 lines] > Is there a way where I can attached my worksheet and show my problem to > anysone? 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 > As a first attempt, you could try posting your formula and describe your > set-up & intents in plain text. [quoted text clipped - 12 lines] > > Is there a way where I can attached my worksheet and show my problem to > > anysone? 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) SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi Max, > [quoted text clipped - 43 lines] > > > Is there a way where I can attached my worksheet and show my problem to > > > anysone? 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? > 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 [quoted text clipped - 79 lines] > > > > Is there a way where I can attached my worksheet and show my problem to > > > > anysone? Try this sample file, which shows things working the way they should as per earlier response: http://www.freefilehosting.net/download/3h57l Sales sumproduct.xls SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hi, > [quoted text clipped - 9 lines] > > Is there any other formulars? Max, Thank you. But unfortunately it still has value #N/A I have uploaded my working file for your reference http://www.freefilehosting.net/download/3h5l2 > Try this sample file, > which shows things working the way they should as per earlier response: [quoted text clipped - 14 lines] > > > > Is there any other formulars? 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 .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Max, > [quoted text clipped - 3 lines] > I have uploaded my working file for your reference > http://www.freefilehosting.net/download/3h5l2 YES!! IT WORKS PERFECTLY FINE NOW. THANKS 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, [quoted text clipped - 16 lines] > > I have uploaded my working file for your reference > > http://www.freefilehosting.net/download/3h5l2 Welcome, glad it's working for you. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > YES!! IT WORKS PERFECTLY FINE NOW. > THANKS MAX :)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.