If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
xldynamic is back
Unfortunately, in my host move I lost some files, so this was an old
version. Also it seems my previous host still holds that email even though they don't have the domain (sic!), so I have had to change it I have updated the whole page, tidied some sections up, added 3 new sections on SUMPRODUCT and VBA, Excel 2007, and Performance, and also split it into 2 pages, the discussion and the examples. -- __________________________________ HTH Bob "Bernd P" wrote in message ... Hello Bob, My email to your webmaster account bounced back. I suggested to change your explanation / example for SUNPORODUCT for an OR condition to change from =SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) to =SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June"))) because Fords in June would get double counted. Kind regards, Bernd |
#12
|
|||
|
|||
xldynamic is back
I have updated the SUMPRODUCT page, tidied some sections up, added 3 new
sections on SUMPRODUCT and VBA, Excel 2007, and Performance, and also split it into 2 pages, the discussion and the examples. -- __________________________________ HTH Bob "Niek Otten" wrote in message news That's great Bob, I used to refer to your site quite often but had to stop doing that. I'll put the links back in my "list of tips" -- Kind regards, Niek Otten Microsoft MVP - Excel "Bob Phillips" wrote in message ... I forgot to mention that the xldynamic site is back up, I have resolved my host provider. I still have to fix the downloads, but eveything else should be working. Bob |
#13
|
|||
|
|||
xldynamic is back
Hello Bob,
Hmm, the OR example is ok now but still a little bit misleading because you can't just add the criteria comparisons for overlapping criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or you might end up double counting overlaps). Regards, Bernd |
#14
|
|||
|
|||
xldynamic is back
It is not trying to be an example for different ranges, but for the same
range. Therefore as it cannot be both conditions there is no need to avoid duplicate counting. At this point it is just introducing OR, to add the further complication of duplications would be too soon IMO. And SIGN is not the only way to test for duplicates, so it doesn't have to be SIGN. -- __________________________________ HTH Bob "Bernd P" wrote in message ... Hello Bob, Hmm, the OR example is ok now but still a little bit misleading because you can't just add the criteria comparisons for overlapping criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or you might end up double counting overlaps). Regards, Bernd |
#15
|
|||
|
|||
xldynamic is back
Hello Bob,
Ok, let us disagree again. IMHO your OR example is misleading. Regards, Bernd PS: I am open to learn something more convincing than SIGN to make the sum of condition checks safe for an OR: http://sulprobil.com/html/sumproduct.html On 28 Sep., 19:12, "Bob Phillips" wrote: It is not trying to be an example for different ranges, but for the same range. Therefore as it cannot be both conditions there is no need to avoid duplicate counting. At this point it is just introducing OR, to add the further complication of duplications would be too soon IMO. And SIGN is not the only way to test for duplicates, so it doesn't have to be SIGN. -- __________________________________ HTH Bob "Bernd P" wrote in message ... Hello Bob, Hmm, the OR example is ok now but still a little bit misleading because you can't just add the criteria comparisons for overlapping criteria. Their sum has to get SIGN()'ed to make it a foolproof OR (or you might end up double counting overlaps). Regards, Bernd |
|
Thread Tools | |
Display Modes | |
|
|