 |
 | 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 / July 2007SUMPRODUCT problem
I have a list where columns B - H are totaled in column I, and columns J - P totaled in column Q. In column R, I want to show the difference between I and Q, but only if there is at least one entry in cols B - H, AND at least one entry in cols J - P. I worked out this, which ought to work but doesn't. =SUMPRODUCT((SUM(J84:P84="")<7)*(SUM(B84:H84="")<7)*(I84-Q84)) Where am I going wrong? This formula returns the difference between I and Q, regardless of whether all the entries in B - H and J - P are blank. Hi Brian, why don't you try =if(sum(i,q)>0,i-q,"") Stefan >I have a list where columns B - H are totaled in column I, and columns J - >P totaled in column Q. In column R, I want to show the difference between I [quoted text clipped - 7 lines] > Where am I going wrong? This formula returns the difference between I and > Q, regardless of whether all the entries in B - H and J - P are blank. Stefan, Thanks for your reply. I should have explained that if there is anything at all (even a zero) in columns B - H or J - P, I want the difference between I and Q to appear to in column R. Brian > Hi Brian, > [quoted text clipped - 15 lines] >> Where am I going wrong? This formula returns the difference between I and >> Q, regardless of whether all the entries in B - H and J - P are blank. =if(sumproduct((B84:H84="")*1, (J84:P84="")*1)=8;"";I84-Q84) should work. hope so! Stefan =if(sumproduct((B84:H84="")*1, (J84:P84="")*1)=8, "", I84-Q84) no semicolons, use commas - sorry! we use semicolons over here in Germany ;-) Stefan > =if(sumproduct((B84:H84="")*1, (J84:P84="")*1)=8;"";I84-Q84) > > should work. hope so! > > Stefan =IF(AND(COUNTA(B2:H2)>0,COUNTA(J2:P2)>0),I2-Q2,"")  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Stefan, > [quoted text clipped - 24 lines] >>> and Q, regardless of whether all the entries in B - H and J - P are >>> blank. Many thanks to all. Those answers work. > =IF(AND(COUNTA(B2:H2)>0,COUNTA(J2:P2)>0),I2-Q2,"") > [quoted text clipped - 26 lines] >>>> and Q, regardless of whether all the entries in B - H and J - P are >>>> blank. Maybe checking those ranges using =count() or =counta(). To check for numeric entries: =if(and(count(b84:h84)>0,count(j84:p84)>0),i84-q84,"no entries") to check for any entries: =if(and(counta(b84:h84)>0,counta(j84:p84)>0),i84-q84,"no entries") > I have a list where columns B - H are totaled in column I, and columns J - P > totaled in column Q. In column R, I want to show the difference between I [quoted text clipped - 7 lines] > Where am I going wrong? This formula returns the difference between I and Q, > regardless of whether all the entries in B - H and J - P are blank. SignatureDave Peterson |
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.