 |
 | 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 / New Users / December 2006not sure if it would be sumif or if
Hi There - hoping someone can help. I need to be able to count and sum a set of data based on a persons name and an out come code. I have tried using the following formula =IF('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4,SUMIF('[Leads Results Template.xls]Status'!$I$1:$I$1000,Q17,'[Leads Results Template.xls]Status'!$M$1:$M$1000),0) In translation if the persons name (Q4) is contained in the lookup range($P$1:$P$1000) I want it to then evaluate a second column for phrase "New Contract" , "New Existing" and "new Order" (if this is present I would like it to add any values in the Value Column only if it equals the persons name tho'. Any suggestions would be appreciated. Cheers Katrina =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4), --('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17), '[Leads Results Template.xls]Status'!$M$1:$M$1000)  Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > Hi There - hoping someone can help. I need to be able to count and sum > a set of data based on a persons name and an out come code. I have [quoted text clipped - 14 lines] > Cheers > Katrina > =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4), > --('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17), [quoted text clipped - 26 lines] > > Cheers > > Katrina The formula given by Bob is correct except that there is a missing open parenthesis in the last part. =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17),('[Leads Results Template.xls]Status'!$M$1:$M$1000)) No there isn't. It is not necessary to enclose the final array in parentheses as there is no condition involved. Signature--- HTH Bob (change the xxxx to gmail if mailing direct) >> =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4), >> --('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17), [quoted text clipped - 34 lines] > Template.xls]Status'!$I$1:$I$1000=Q17),('[Leads Results > Template.xls]Status'!$M$1:$M$1000)) What's in Q17? If that's "New Contract" then perhaps =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),--('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17),'[Leads Results Template.xls]Status'!$M$1:$M$1000) If you want to check for any of the terms you listed and these are in Q17:Q19 for instance then amend to =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4),--ISNUMBER(MATCH('[Leads Results Template.xls]Status'!$I$1:$I$1000,Q17:Q19,0)),'[Leads Results Template.xls]Status'!$M$1:$M$1000) > Hi There - hoping someone can help. I need to be able to count and sum > a set of data based on a persons name and an out come code. I have [quoted text clipped - 14 lines] > Cheers > Katrina Thanks for that - although neither seem to work I get a #n/A response I have tried breaking the formula down using the wizard but nothing seems to work ... should i have some sort of analysis tool installed? i have also tried using the following formula (have been looking on other sites for answers as well but it too returns an #n/a value =SUMPRODUCT('[Leads Results Template.xls]Status'!$P$1:$P$853=Q3)*('[Leads Results Template.xls]Status'!$P$1:$P$853=P3)*('[Leads Results Template.xls]Status'!$M$1:$M$853) im stumped im sorry ... > What's in Q17? If that's "New Contract" then perhaps > [quoted text clipped - 12 lines] > > "> > Katrina, Does the other workbook have #N/A in any of the cells? Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > Thanks for that - although neither seem to work I get a #n/A response > [quoted text clipped - 27 lines] >> >> "> > HI Bob, Thanks so much for your assistance ... it did have 3 #n/a's when removed formula worked fine ... now my next task is to count the number of outomes of contract, new order and reorder per persons name. Im assuming i can use a similar sort of formula. thanks again Katrina =SUMPRODUCT(--('[Leads Results Template.xls]Status'!$P$1:$P$1000=Q4), --('[Leads Results Template.xls]Status'!$I$1:$I$1000=Q17)) Signature--- HTH Bob (change the xxxx to gmail if mailing direct) > HI Bob, > [quoted text clipped - 5 lines] > thanks again > Katrina
|
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.