 |
 | 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 / January 2008Sumproduct Question
=SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)) This above formula achieves the desired result. However, when I add a fourth column (below) I get a Value error. I think I'm close...any ideas? =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too. Second, do you have any text values in M25:M2000--or any errors in that range? If you have some text, you can use a different formula to ignore text: =SUMPRODUCT(--($E$25:$E$2000=4), --($F$25:$F$2000=6.5), $P$25:$P$2000, $M$25:$M$2000) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html === ps. When you're looking for text or errors, remember to look in hidden rows, too. > =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)) > [quoted text clipped - 4 lines] > > =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000))  SignatureDave Peterson >First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too. No. It was correct. >Second, do you have any text values in M25:M2000--or any errors in that range? None. >If you have some text, you can use a different formula to ignore text: > >=SUMPRODUCT(--($E$25:$E$2000=4), > --($F$25:$F$2000=6.5), > $P$25:$P$2000, > $M$25:$M$2000) Doing it this way doesn't return the value error, but it gives different answers. I tallied the results by hand and for some reason they are off. >Adjust the ranges to match--but you can't use whole columns (except in xl2007). > [quoted text clipped - 19 lines] >> >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) Are you sure that you don't have a #VALUE in one of the cells? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >>First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, >>too. [quoted text clipped - 43 lines] >>> >>> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) Yes I'm quite sure. I checked manually, and also many other computations are used with those columns of data and they return proper answers. >Are you sure that you don't have a #VALUE in one of the cells? Select that range Edit|goto|special Check constants and errors (and uncheck everything else) Then try the same thing with formulas and errors. If you have the data filtered, make sure you show all the rows. > Yes I'm quite sure. I checked manually, and also many other > computations are used with those columns of data and they return > proper answers. > > >Are you sure that you don't have a #VALUE in one of the cells? SignatureDave Peterson I did both and got an answer of "no cells were found." >Select that range >Edit|goto|special [quoted text clipped - 9 lines] >> >> >Are you sure that you don't have a #VALUE in one of the cells? If you put: =counta(m25:m2000) in an empty cell and =count(m25:m2000) in another What do these formulas return? > >First, I'd watch those addresses. I bet you want to use $P$25:$P$2000, too. > [quoted text clipped - 38 lines] > >> > >> =SUMPRODUCT(($E$25:$E$2000=4)*($F$25:$F$2000=6.5)*(P25:P2000)*($M$25:$M$2000)) SignatureDave Peterson 221, and 118 respectively. >=counta(m25:m2000) That means that there are 3 values that are not numbers. =counta() returns the number of cells with something in them. =count() returns the number of cells with numbers in them. Select the range edit|goto|special and look for numbers or text or blanks Look in values or formulas or both. > 221, and 118 respectively. > > >=counta(m25:m2000) SignatureDave Peterson OK, checked all of that out. This is the formula I have in the fist cell. =IF(I26="","",I26-H26) I drag it down the column. For every new day that data is entered I skip a space. I couldn't find the three cells. >That means that there are 3 values that are not numbers. > [quoted text clipped - 10 lines] >> >> >=counta(m25:m2000) Do you have the worksheet filtered? Maybe your error or text value is in one of the hidden rows??? > OK, checked all of that out. This is the formula I have in the fist > cell. [quoted text clipped - 18 lines] > >> > >> >=counta(m25:m2000) SignatureDave Peterson You *should not* use the asterisk form of Sumproduct() when you have your calculating column(s) populated by formulas that may return zero length strings (nulls - "" ), as you *do* have! These returns are considered TEXT, and will generate the #Value! error when Column I is empty, causing a null ( "" ) return.in Column M. The asterisk form will calculate text numbers, as long as they look like numbers, but will error out on all other text values that are non-numeric. The unary form (suggested by Dave), will by-pass text *AND* numeric text, and just calculate "true" numbers. So, if your calculations are different using Dave's suggestion, I would guess that you have *both* numeric and text numbers present in your datalist. If you would/could revise your formula to: =IF(I26="",0,I26-H26) then you could continue using the asterisk form, since zero would replace the text null ( "" ). You might try it and post back with results. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > OK, checked all of that out. This is the formula I have in the fist > cell. [quoted text clipped - 18 lines] >>> >>> >=counta(m25:m2000) Not sure I understand all of it, but it worked. CountA and Count both return 289, so I guess that did the trick. The only problem now is that $0.00 shows up in the skipped rows, so I guess I'll have to use condiditional formatting to get rid of them. Appreciate the advice. Thanks a lot. >You *should not* use the asterisk form of Sumproduct() when you have your >calculating column(s) populated by formulas that may return zero length [quoted text clipped - 19 lines] > >You might try it and post back with results. Found a gliltch. Now this formula in a column two columns over no longer works. =IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),"")) >Not sure I understand all of it, but it worked. CountA and Count both >return 289, so I guess that did the trick. The only problem now is [quoted text clipped - 26 lines] >> >>You might try it and post back with results. Look, if data is to be keyed in, or imported in, the asterisk form of Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're populating your calculating columns with formulas returning nulls, you *should* be using the unary form, as Dave suggested. Now that you've revised your formula to return zeroes, compare both forms of the function and see if they both return the same result. If they don't, that means that you have true numbers, and text that looks like numbers, in your datalist. That's not a good thing to have happening, at any time! You can use a 'helper" column, and reference it to any questionable columns, to see if all your numbers are numeric numbers. Simply use this in any vacant column: =Isnumber(M26) and drag it down to see if every row returns a TRUE. If you see a FALSE, that's the row(s) to fix! On the other hand, if you insist on using the asterisk form, and you're going to use CF to fix the display, then you'll have to revise *all* your other formulas to match the revised value of your calculating columns. FROM: =IF(M28="","",IF(M29="",SUM(M$26:M28)-SUM(O$26:O27),"")) TO: =IF(M28=0,0,IF(M29=0,SUM(M$26:M28)-SUM(O$26:O27),0)) It *may* become a nightmare trying to change everything around. Your choice! SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Found a gliltch. Now this formula in a column two columns over no > longer works. [quoted text clipped - 33 lines] >>> >>>You might try it and post back with results. I had to change the formula in three columns as you suggested to make everything work. Thanks for your help. >Look, if data is to be keyed in, or imported in, the asterisk form of >Sumproduct is the way to go ... warns you of mistakes ... BUT ... if you're [quoted text clipped - 31 lines] > >Your choice! You're very welcome, and thank you for the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >I had to change the formula in three columns as you suggested to make > everything work. Thanks for your help. [quoted text clipped - 37 lines] >> >>Your choice! |
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.