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.
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”))
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.
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
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.
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.
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!
@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.
@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 🙂
Please update the links to download the sample files
Great. Now i can say thank you!,