Until a few months back I was a member of Experts-Exchange. I’ve found the site a great tool for those little problems you know countless others have encountered and could solve for me in a matter of seconds…. Anyway- this year I was thinking twice about renewing when I re-discovered Stack Exchange a very similar tool but this time at no cost. Stack Exchange has been invaluable in assisting me with a number of recent problems…

SUMIF with multiple criteria in Excel 2003

What I wanted to do seemed relatively straightforward and google threw up a lot of results but the majority of recommended solutions were for Excel 2007 and above. The remaining solutions simply didn’t seem to work?

Here’s the question/answer on Stack Overflow

In the below screenshot I was trying to sum all income for org_code (row 18) and month (column A).

It turned out I was making a few fundamental mistakes:

  • org_code is being held as a string in data table (the left-hand alignment helped Jesse spot this and point me in the right direction!
  • none of the possible solutions seem to like named ranges or evaluating entire columns (A1:A1000 works fine but A:A does not)

I’d like to find some more time to look at this (though unlikely I will). I should also have been able to use SUM and IF in an array formula too.

The working solution looks "something like"

=SUMPRODUCT(D1:D8,--(A1:A8=OrgRef),--(C1:C8=MonthRef))
Thanks Jesse!

ESXi problems

1) ESXi 5.0 relapsing to an old configuration on reboot

I’m still unsure what caused this but I did find that volumes weren’t mounting as expecting during the hypervisor boot process. Choosing recovery mode (shift-r) option during boot successfully solved the issue however.

2) Unable to power on ESXi 5.0 host from vSphere

Still a bit of a puzzle but the primary reason appears to’ve been that the host had to not only be managed to vCenter, but also had to be part of a "cluster". Once I created a cluster and moved the host into it I was able to place the physical machine into standby mode. Unfortunately, I still don’t appear to be able to power the host from an off state. If I make any further developments I will try and update this post.

Cat5e cable with cat6 connectors

There appears to be a lot of contradicting information online about the back-ward compatibility of cat6 connectors but from this experience I would have to rule that cat6 connectors are NOT backward compatible with cat5e cable. Question on Server Fault.

Let’s hope I continue to gain a lot from these sites!

L

Advertisements