 |
 | 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 / February 2008Understanding SUMPRODUCT()
I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even values between A1 and A20. The following attempts do not work: =SUMPRODUCT(--(--ISEVEN(A1:A20)=1)) =SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE")) any help will be appreciated.  Signaturejake ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a range However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email >I am trying to use SUMPRODUCT instead of COUNTIF to count the number of >even [quoted text clipped - 4 lines] > > any help will be appreciated. Thanks! One more question...you mentioned that ISEVEN can not generate an array. Are ISEVEN() and MOD() really that different? Can any logical function return an array? Signaturejake > ISEVEN(A1:A20) cannot generate an array, so it cannot be used with a range > However =SUMPRODUCT(--(MOD(A1:A20,2)=0)) will work [quoted text clipped - 7 lines] > > > > any help will be appreciated. Clearly not, as ISEVEN doesn't. There seems to be no logic as to which functions will and which will not handle an array, for instance WEEKDAY does, WEEKNUM doesn't. My personal view FWIIW is that it is all down to the developer that crafted the particular function. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thanks! > [quoted text clipped - 15 lines] >> > >> > any help will be appreciated. Thank you Bob. Do you know of any reference, any book, any website, that covers this topic? I guess I can use trial & error, but I hate to reinvent the wheel if there is a good published source. Signaturejake > Clearly not, as ISEVEN doesn't. > [quoted text clipped - 22 lines] > >> > > >> > any help will be appreciated. I am afraid I don't. http://www.xldynamic.com/source/xld.SUMPRODUCT.html has the most comprehensive coverage of SUMPRODUCT, but I know for a fact that it doesn't cover that topic. As I said, I know of no logic in which work, which don't, so I think it is just trial and error. Maybe I will do something and publish it as well.. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thank you Bob. Do you know of any reference, any book, any website, that > covers this topic? [quoted text clipped - 30 lines] >> >> > >> >> > any help will be appreciated. Thanks Bob. Signaturejake > I am afraid I don't. http://www.xldynamic.com/source/xld.SUMPRODUCT.html has > the most comprehensive coverage of SUMPRODUCT, but I know for a fact that it [quoted text clipped - 36 lines] > >> >> > > >> >> > any help will be appreciated. Several ways but using sumproduct try this =SUMPRODUCT(--(MOD(A1:A20,2)=0)) and for odd numbers =SUMPRODUCT(--(MOD(A1:A20,2)=1)) Mike > I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even > values between A1 and A20. The following attempts do not work: [quoted text clipped - 3 lines] > > any help will be appreciated. Thank you ! Signaturejake > Several ways but using sumproduct try this > [quoted text clipped - 12 lines] > > > > any help will be appreciated. Don't thank me yet because I've changed my mind, the previous formula interprets a blank cell as even so use this instead =SUMPRODUCT(--(A1:A20<>""),--(MOD(A1:A20,2)=0)) Mike > Thank you ! > [quoted text clipped - 14 lines] > > > > > > any help will be appreciated. >I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even >values between A1 and A20. The following attempts do not work: [quoted text clipped - 3 lines] > >any help will be appreciated. I believe the problem is that ISEVEN will only work on a single cell, and cannot return an array. To test for even numbers, you could use: =SUMPRODUCT(ISNUMBER(A1:A20)*(MOD(A1:A20,2)=0)) or, if there will be no blanks at all: =SUMPRODUCT(--(MOD(A1:A20,2)=0)) --ron Thank you for your very rapid reply. Is there a list somewhere of worksheet functions that can and cannot return arrays ?? I looked in Excel Help for ISEVEN and there was no mention of this limitation. Signaturejake > >I am trying to use SUMPRODUCT instead of COUNTIF to count the number of even > >values between A1 and A20. The following attempts do not work: [quoted text clipped - 15 lines] > =SUMPRODUCT(--(MOD(A1:A20,2)=0)) > --ron >Thank you for your very rapid reply. Is there a list somewhere of worksheet >functions that can and cannot return arrays ?? > >I looked in Excel Help for ISEVEN and there was no mention of this limitation. I'm not aware of any such list. But it has been my experience that many of the functions in the Analysis ToolPak (pre Excel 2007) will not return arrays. You can check this by using Tools/Formula Auditing/Evaluate formula and observing when, with a multi-cell argument, an array is returned. Or you can select the relevant part of the function in the function bar, and hit <f9> to see the intermediate result. --ron Thanks Ron Signaturejake > >Thank you for your very rapid reply. Is there a list somewhere of worksheet > >functions that can and cannot return arrays ?? [quoted text clipped - 9 lines] > see the intermediate result. > --ron The help file refers to SUMPRODUCT as summing products. That is true, but it can do other things such as; A1 = 3, A2 = 5, A3 = 7, B1 = 2, B2 = 2 B3 = 2 C1: =SUMPRODUCT(A1:A3*B1:B3) result: 30 C2: =SUMPRODUCT(A1:A3/B1:B3) result: 7.5 C3: =SUMPRODUCT(A1:A3+B1:B3) result: 21 C4: =SUMPRODUCT(A1:A3-B1:B3) result: 9 C5: =SUMPRODUCT(A1:A3^B1:B3) result: 83 Tyro >I am trying to use SUMPRODUCT instead of COUNTIF to count the number of >even [quoted text clipped - 4 lines] > > any help will be appreciated. I think he probably knows that, what he didn't know was nothing to do with SP per se, but to do with why some functions can handles arrays and some can't. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > The help file refers to SUMPRODUCT as summing products. That is true, but > it can do other things such as; [quoted text clipped - 21 lines] >> >> any help will be appreciated. I mention it only because none of the documentation I have ever seen about SUMPRODUCT, including Excel help, mention that. Tyro >I think he probably knows that, what he didn't know was nothing to do with >SP per se, but to do with why some functions can handles arrays and some [quoted text clipped - 25 lines] >>> >>> any help will be appreciated. I should clarify. Excel help says SUMPRODUCT sums the products. Products are produced only by multiplication. So, the Excel help file implies that SUMPRODUCT does only multiplication. Tyro >I think he probably knows that, what he didn't know was nothing to do with >SP per se, but to do with why some functions can handles arrays and some [quoted text clipped - 25 lines] >>> >>> any help will be appreciated. The fact that he was trying =SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE")) suggests that he knows that, at least it does to me. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >I should clarify. Excel help says SUMPRODUCT sums the products. Products >are produced only by multiplication. So, the Excel help file implies that [quoted text clipped - 31 lines] >>>> >>>> any help will be appreciated. |
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.