| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Understanding 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. -- jake |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
Several ways but using sumproduct try this
=SUMPRODUCT(--(MOD(A1:A20,2)=0)) and for odd numbers =SUMPRODUCT(--(MOD(A1:A20,2)=1)) Mike "Jakobshavn Isbrae" wrote: 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. -- jake |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
On Sun, 3 Feb 2008 05:13:01 -0800, Jakobshavn Isbrae
wrote: 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. 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
Thank you !
-- jake "Mike H" wrote: Several ways but using sumproduct try this =SUMPRODUCT(--(MOD(A1:A20,2)=0)) and for odd numbers =SUMPRODUCT(--(MOD(A1:A20,2)=1)) Mike "Jakobshavn Isbrae" wrote: 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. -- jake |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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 "Jakobshavn Isbrae" wrote: Thank you ! -- jake "Mike H" wrote: Several ways but using sumproduct try this =SUMPRODUCT(--(MOD(A1:A20,2)=0)) and for odd numbers =SUMPRODUCT(--(MOD(A1:A20,2)=1)) Mike "Jakobshavn Isbrae" wrote: 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. -- jake |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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? -- jake "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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. -- jake "Ron Rosenfeld" wrote: On Sun, 3 Feb 2008 05:13:01 -0800, Jakobshavn Isbrae wrote: 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. 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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? -- jake "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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. -- jake "Bob Phillips" wrote: 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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? -- jake "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
On Sun, 3 Feb 2008 05:45:01 -0800, Jakobshavn Isbrae
wrote: 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. -- jake 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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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.. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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. -- jake "Bob Phillips" wrote: 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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? -- jake "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
Thanks Bob.
-- jake "Bob Phillips" wrote: 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.. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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. -- jake "Bob Phillips" wrote: 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jakobshavn Isbrae" wrote in message ... 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? -- jake "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
Thanks Ron
-- jake "Ron Rosenfeld" wrote: On Sun, 3 Feb 2008 05:45:01 -0800, Jakobshavn Isbrae wrote: 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. -- jake 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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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 "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... 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 "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
I mention it only because none of the documentation I have ever seen about
SUMPRODUCT, including Excel help, mention that. Tyro "Bob Phillips" wrote in message ... 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... 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 "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
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 "Bob Phillips" wrote in message ... 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... 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 "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Understanding SUMPRODUCT()
The fact that he was trying
=SUMPRODUCT(--(ISEVEN(A1:A20)="TRUE")) suggests that he knows that, at least it does to me. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... 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 "Bob Phillips" wrote in message ... 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. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Tyro" wrote in message ... 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 "Jakobshavn Isbrae" wrote in message ... 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. -- jake |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Understanding .End(xlUp) (1,1) | Excel Discussion (Misc queries) | |||
| Understanding SUMPRODUCT | Excel Worksheet Functions | |||
| Understanding a formula | Excel Worksheet Functions | |||
| Understanding a formula | Excel Worksheet Functions | |||
| Understanding this formula | Excel Worksheet Functions | |||
Linear Mode
