Ego, Vlookup, and Matrix math

Surprise!

Recently I had a very nice surprise.  

This is a personal blog, and I do very little to promote it.  As a result, I get few views, mostly from Google searches that probably hit my posts' key words.  When I looked at the views earlier this week I noticed that one came from Excel Strategies.  Heck!  That's a real site, one that belongs to a someone in the business of Excel services!  OOOOH WHEEEEE!

Boost!

I looked into it a little further and found that one of my posts was cited in one of their blog posts!  It was referenced, by link, and described as a "brilliant rebuttal" to the contention that INDEX(MATCH()) should always be used instead of VLOOKUP().  What made it all the more exciting was the others in the conversation were MVPs.

Follow-up

I dropped a note, thanking Alex for considering my opinion worthwhile.  We have since exchanged emails and he invited me to make comments on his blog.  (For whatever reason, he said he could not comment here...that is something I need to fix.)

Reality

One of his other posts I noticed was one that had to do with 7 ways to sum conditionally.  Actually, there were 8, but who's to quibble.  He marked the 8th as a bonus.

As I posted on as a comment. there is nothing like knowing multiple ways to solve similar problems, each with their own uses driven by the situation, of course!  

His direct question to readers was which of the methods we used.  

I definitely use SUMIF and SUMIFS most, following by PivotTables and the SubTotal.  DSUM I've only used in Access.  

I'm not fluent in SUMPRODUCT nor the array formulas, so I use them seldom.  That's probably because my math education ended with Business Calc & Business Statistics.  Matrix math hasn't taken hold in my gray matter.  For example, sometimes I see column arguments separated by a comma and sometimes by the multiplication sign.  I understand, to some degree, what the formula does in each case but I don't understand when each is appropriate.  IOW, I can read and imitate but writing from scratch would be a  challenge.

Back up again

I had mentioned the following in an email to Alex, and included this (with some edits since) in my comment.

A couple of months or so ago I ran into a need to average across a row, ignoring hidden columns.  Kind of like SubTotal, only sideways.  Of course, SubTotal() only works when considering hidden rows.  I devised a VBA UDF to ignore hidden columns as well as rows, complementing/replacing  SubTotal. The process I went through to get to this is on my personal blog (certainly not a competitor to this one!) on a page called "Beyond SubTotal()".