 |
 | 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 / Worksheet Functions / March 2008SUMPRODUCT with Multiple Criteria
I have three criteria with this formula and it brings back a $0 value, when I know there is a $ amount that should have be calculated. I am working in one workbook and linking to another. What have I done wrong? =SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37="P"),--('[Workbook Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) You're testing one range twice for 2 different criteria. While one test might be true the other *has* to be false so T*F=0. In other words: if it's "A" then it *can't* be "P" and vice versa. Try it like this: =SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook Name.xls]Name'!$M$2:$M$37="Y")*'[Workbook Name.xls]Name'!$I$2:$I$37)  SignatureBiff Microsoft Excel MVP >I have three criteria with this formula and it brings back a $0 value, when >I [quoted text clipped - 5 lines] > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) You are brilliant!! It worked! Thank you for the help. > You're testing one range twice for 2 different criteria. While one test > might be true the other *has* to be false so T*F=0. In other words: if it's [quoted text clipped - 14 lines] > > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook > > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > You are brilliant!! It worked! Thank you for the help. > [quoted text clipped - 19 lines] >> > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) One other question hopefully you can help with; I have a similar situation with having to count the number of occurences with three criteria (two of which are in the same column). I tried to modify my formula, but it isn't working. =SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}),--('[Workbook Name.xls]Name'!$F$2:$F$37="1")) > You're welcome. Thanks for the feedback! > [quoted text clipped - 21 lines] > >> > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook > >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) Probably a number, not text =SUMPRODUCT(--('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"}), --('[Workbook Name.xls]Name'!$F$2:$F$37=1)) Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > One other question hopefully you can help with; I have a similar situation > with having to count the number of occurences with three criteria (two of [quoted text clipped - 35 lines] >> >> > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook >> >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) I am getting a #VALUE error. Any other suggestions? > Probably a number, not text > [quoted text clipped - 40 lines] > >> >> > Name.xls]Name'!$O$2:$O$37="A"),--('[Workbook > >> >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook Name.xls]Name'!$I$2:$I$37) that suggests you have an error in one of the cells being summed, probably F2:F37 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I am getting a #VALUE error. Any other suggestions? > [quoted text clipped - 49 lines] >> >> >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook >> >> >> > Name.xls]Name'!$I$2:$I$37) Figured it out: =SUMPRODUCT(('[Workbook Name.xls]Name'!$O$2:$O$37={"A","P"})*('[Workbook Name.xls]Name'!$F$2:$F$37=1)) > that suggests you have an error in one of the cells being summed, probably > F2:F37 [quoted text clipped - 52 lines] > >> >> >> > Name.xls]Name'!$M$2:$M$37="Y"),'[Workbook > >> >> >> > Name.xls]Name'!$I$2:$I$37) |
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.