xldynamic Returns

















Normal







0























false







false







false































































MicrosoftInternetExplorer4

























































I have a website http://www.xldynamic.com/ which tries to provide some useful Excel information, different to the many other Exel websites. This site includes what is probably the most useful SUMPRODUCT discussion on the web.

Around about August 2008, my host provider, 123-reg.co.uk, started to tear down my site on or around the 20th of every month, due to exceeding my bandwidth limit (I admit, until then I had not been aware I had a limit). I was confused as to why this should happen, my site was popular, but was never one of the most frequented sites, and I had no new material to tempt anyone. Unfortunately, seeking clarification from 123-reg was a frustrating exercise, I was just bounced back with cut-and-pasted responses that did little to address my actual question.

So, when my renewal came up, I was looking to be off. I found a new provider, and wanted to move my site across. Again, 123-reg were just as unhelpful in transferring my domain as they had been in helping me to understand the problems, so it has taken a while.

All of this is a long preamble to saying my site is back in operation, and it looks as it did before. It is my hope and intention to give it a refresh when the time allows, make it more dynamic, and to add some new content, but for now I am just happy for it to be back up.

One thing I have managed to do is to update the SUMPRODUCT page. This page has been split into two parts, the first part deals with the mechanics of SUMPRODUCT, how it works, and considerations of this, whilst the second part has all of the examples. In addition, the first part has 3 new sections:

-          Conditional Counting And Summing in VBA – using SUMPRODUCT
in VBA

-          SUMPRODUCT and Excel 2007 – the implications of
COUNTIFS and SUMIFS on SUMPRODUCT

-          Performance considerations – of using SUMPRODUCT


This page is going to get another overhaul soon, but in the meantime I hope that these changes are useful.


I would just add that my new host provider orcsWeb, has been immeasurably better in providing technical support. So far, it seems to be a great service.


 

10 thoughts on “xldynamic Returns”

  1. With the Introduction of Sum ifs/Count ifs from 2007 there are only a few situations where you would be forced to use sum product

    1) Mutual exclusive OR criteria – Sum/Count of Car = BMW, Month = June
    =SUMPRODUCT(–(((Make=”BMW”)+(Month=”MAY”))>=1),(Price))

    2) Sum based on 2 criteria where one Criteria in in a Row and the other in a column
    N S E W
    A 1 4 7 10
    B 2 5 8 10
    C 3 6 9 3

    =Sumproduct((Name=”B”)*(Region=”E”),(Rectangular Rng))

    3) Count the number of i’s in Phillips
    =SUMPRODUCT(–(MID(A1,ROW(1:10),1)=”i”))

  2. I think there are a lot more, such as all those situations where some part of the dat is being tested for,  a particular month within a date range for instace.

    Your item 3 is a similar example of just that.

    I think SUMPRODUCT is far from dead, because SUMIFS and COUNTIFS didn’t go nearly far enough, they just took a simplistic approach.

  3. You are right SumIFS/CountIFS didnt go the distance…

    However there are couple of things possible with SumIF(S)/CountIF(S) that are not possible with Sumproduct

    1) SUMPRODUCT(1/COUNTIF(Name,Name)) – Give a Count of the Distinct Items in Range Name.
    The CountIF part cant be replaced with Sumproduct

    2) CountIF(Name,”<=”&Name) to give an array of Ranks of all names in the Range Name

  4. But that is SUMPRODUCT not COUNTIFS, as your originally assertion stated. You could do this in Excel 2003 and earlier, so SUMIFS and COUNTIFS hasn’t altered the landscape one iota there either. You are making my point.

  5. SUMIFS/COUNTIFS/AVERAGEIFS can’t handle any type of OR, only AND.

    =SUMPRODUCT(–((X=”x”)+(Y=”y”)>0))

    still requires SUMPRODUCT.

    If there were only a BUILT-IN SQL.REQUEST function with a reasonable query optimizer when the table was a static range reference, there’d be no need for either COUNTIFS/etc or SUMPRODUCT for conditional aggregation calcs.

    Or Microsoft could finally update DCOUNT/etc to use criteria expressions rather than criteria ranges. Even if Microsoft can’t bring itself to provide new syntax for such expressions, they could allow 2D arrays in place of references to criteria ranges. It probably won’t ever happen because I think the requirements that 1st and 3rd args to DCOUNT or SUMIF are due to coding directly against ranges, so adding abstraction would require a major rewrite.

  6. Interesting to hear about 123, Bob, I see them advertise all the time in Micro Mart, I’ve thought about using then a few times, but have so far stuck to my constant provider who have been very good. Glad I did now, this stuff my my blood boil!

  7. @Harlan, agree 100%. The database functions are so under utilised it is almost criminal, but they are more difficult to use, and of course as you point out, they lack the ability to use expressions. If they sorted that, most of these array solutions we come up with could be dipensed with.

    We should start a campaign to a) prmote the database functions, and b) to ask MS to improve them (won’t happen). I feel a blog coming on.

  8. @ross, I was gentle in my remarks, my actual feelings are much stronger. I was happy with them for many years, but of course that was when everything was fine, and I had no need of support. As soon as I needed support, they failed miserably for me.

    If you are happy with your provider, would you like to name them, we should praise the good as well as condemn the bad, and you never know, I might want to switch again :-)

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>