 |
 | 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 / November 2005Sumproduct
I use this formula: =SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="Directed");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60);Sheet1!$N$4:$N$903) The values in Col N are not numbers. The formula returns 0 (zero). Is there a way to modify the formula to count the number of cells that satisfy the 4 criteria ? Thank you in advance. First, I don't know why it's even letting you commit that formula with semi-colon's to separate each condition. SUMPRODUCT is set up to allow comma's for the breaks, so you might want to change that. Second, the way you have the formula set up now, it sums Sheet1!$N$4:$N$903 if all 4 conditions are met (which is why it's returning 0, because there are no numerical values). What are you actually trying to do with the values in column N? You need to specifially set a condition for what you are trying to count. For example, if you are trying to count the number of non-missing values in column N when all 4 conditions are met, change to this: --(Sheet1!$N$4:$N$903<>"") Does that make sense?  SignatureRegards, Dave > I use this formula: > [quoted text clipped - 6 lines] > > Thank you in advance. Thank you. It does make sense. Any chance I can set the criteria --(Sheet1!$N$4:$N$903<>"") to only count Unique Values ? > First, I don't know why it's even letting you commit that formula with > semi-colon's to separate each condition. SUMPRODUCT is set up to allow [quoted text clipped - 21 lines] > > > > Thank you in advance. To count the unique values in Column N where Columns I, Y, AB, and AC meet your criteria, try... =SUM(IF(FREQUENCY(IF((Sheet1!$I$4:$I$903=Sheet2!E11)*(Sheet1!$Y$4:$Y$903= Sheet2!G$10)*(Sheet1!$AB$4:$AB$903="Directed")*(Sheet1!$AC$4:$AC$903=60)* (Sheet1!$N$4:$N$903<>""),MATCH(Sheet1!$N$4:$N$903,Sheet1!$N$4:$N$903,0)), ROW(Sheet1!$N$4:$N$903)-ROW(Sheet1!$N$4)+1)>0,1)) ...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! > Thank you. It does make sense. > [quoted text clipped - 31 lines] > > > > > > Thank you in advance. Not sure how to implement your suggestion. I tried to replace the last argument in my original formula with your suggestion and it returned 0 (zero). Is this correct ? > First, I don't know why it's even letting you commit that formula with > semi-colon's to separate each condition. SUMPRODUCT is set up to allow [quoted text clipped - 21 lines] > > > > Thank you in advance. Assuming that your version of Excel uses a semi-colon instead of a comma as a separator, and that the '4 criteria' refers to Columns Y, AB, I, and AC, try.. =SUMPRODUCT(--(Sheet1!$Y$4:$Y$903=Sheet2!G$10);--(Sheet1!$AB$4:$AB$903="D irected");--(Sheet1!$I$4:$I$903=Sheet2!E11);--(Sheet1!$AC$4:$AC$903=60)) Hope this helps! > I use this formula: > [quoted text clipped - 8 lines] > > Thank you in advance. |
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
|
©2009 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.