 |
 | 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 / April 2008Sumproduct with text and numbers in cells
Excel 2007 I want to sum a column that each cell has a number or text in it based on the conditions of that cell having a number and the results of other arrays. I keep getting #value error even though I use the double unary (--). Can I do this using sumproduct or do I need to use the sumifs? I was hoping to use the sumproduct to keep the workbook compatible with 2003 user. Thanks,  SignatureLee Coleman What exactly are you trying? =SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10)) Sums values in column C where A = "Text" and b (the value) = 1. Is this what you're doing? SignatureHTH, Barb Reinhardt > Excel 2007 > [quoted text clipped - 4 lines] > the sumproduct to keep the workbook compatible with 2003 user. > Thanks, I have tried something like this: =sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:10>0),C1:C10) I want to sum column C based on the date column A equal to the week number in A1 on another sheet. The problem is the C column has numbers or text, not both, and I want to sum the numbers in the C column that are greater than 0. I get the #value error based on the above formula. Thanks, Lee > What exactly are you trying? > [quoted text clipped - 15 lines] >> the sumproduct to keep the workbook compatible with 2003 user. >> Thanks, =sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C10>0),(C1:C10)) SignatureHTH, Barb Reinhardt > I have tried something like this: > =sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:10>0),C1:C10) [quoted text clipped - 24 lines] > >> the sumproduct to keep the workbook compatible with 2003 user. > >> Thanks, =SUMPRODUCT(--(WEEKNUM(Spring4!$N$2:$N$2000,1)='Weekly Plug Tray'!$A4),--(Spring4!$P$2:$P$2000>0),(Spring4!$P$2:$P$2000)) The above formula is what I have and doesn't work. However, if I use the formula below with a helper column it works: =SUMPRODUCT(--(Spring4!$U$2:$U$2000='Weekly Plug Tray'!$A5),--(Spring4!$P$2:$P$2000>0),(Spring4!$P$2:$P$2000)) My problem may be with the weeknum function rather than with the sumproduct. any thoughts? Thanks again. Lee > =sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C10>0),(C1:C10)) > [quoted text clipped - 32 lines] >> >> the sumproduct to keep the workbook compatible with 2003 user. >> >> Thanks, A helper column would be the easiest way to go, I think, but if you really wanted to avoid that...... You can replicate =WEEKNUM(A1,1) with =INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1))/7)+2 so you could change your formula to: =SUMPRODUCT(--(INT((Spring4!$N$2:$N$2000-DATE(YEAR(Spring4!$N$2:$N$2000),1,1)-WEEKDAY(Spring4!$N$2:$N$2000))/7)+2='Weekly Plug Tray'!$A4),--(Spring4!$P$2:$P$2000>0),(Spring4!$P$2:$P$2000)) > =SUMPRODUCT(--(WEEKNUM(Spring4!$N$2:$N$2000,1)='Weekly Plug > Tray'!$A4),--(Spring4!$P$2:$P$2000>0),(Spring4!$P$2:$P$2000)) [quoted text clipped - 46 lines] > >> >> the sumproduct to keep the workbook compatible with 2003 user. > >> >> Thanks, The WEEKNUM function won't work on arrays. SignatureBiff Microsoft Excel MVP > =sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C10>0),(C1:C10)) > [quoted text clipped - 32 lines] >> >> the sumproduct to keep the workbook compatible with 2003 user. >> >> Thanks, Thanks for telling me. It will work with the helper column. How do you know what will and won't work? Lee > The WEEKNUM function won't work on arrays. > [quoted text clipped - 34 lines] >>> >> the sumproduct to keep the workbook compatible with 2003 user. >>> >> Thanks, >How do you know what will and won't work? Experience combined with trial and error. I don't think I've ever read in the Excel help files that such and such function(s) do not work with arrays. So, it's trial and error. SignatureBiff Microsoft Excel MVP > Thanks for telling me. It will work with the helper column. How do you > know what will and won't work? [quoted text clipped - 37 lines] >>>> >> the sumproduct to keep the workbook compatible with 2003 user. >>>> >> Thanks, Thanks, I've learned something today. :) Barb Reinhardt > >How do you know what will and won't work? > [quoted text clipped - 43 lines] > >>>> >> the sumproduct to keep the workbook compatible with 2003 user. > >>>> >> Thanks, Thanks to all for helping. I don't feel quite so ignorant knowing that all functions do not get along. (Sounds like some programers built a little of their persona into them.) Again, many thanks to all. Lee > >How do you know what will and won't work? > > Experience combined with trial and error. I don't think I've ever read in > the Excel help files that such and such function(s) do not work with > arrays. So, it's trial and error.
|
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.