Saturday, December 21, 2013

Question at work....

Okay folks, I found a winner yesterday while at work.  I was asked by a colleague if there was a way to sum the value of multiple items from an array off to the side of a data table. In other words, he wanted to find the values of items a,b,c within a data table that contains multiple occurrences of items a-z and their values.  It looks something like:

So, I Googled "Sum", "Criteria", "Array"

The formula that I eventually came up with was found @

The formula looks like: =SUM(SUMIF(A3:A11,{"a","b"},B3:B11))

The interesting thing about this formula, is that the criteria portion of the SumIf() formula is in {} and listed as an array.  However, it's important to note that so far as I've been able to find, you can't use cell references within that array - it will just give you an error and pop up a message. So, the values you're looking for, must be actually typed out and within quotes.

Something else I noticed as I was toying around with this formula was that if you leave off the outer "Sum() formula, the sumif() formula only returns the sum of the "a" values (=50).  When the Sum() is added around the SumIF() formula, all values within the criteria are added (=63).

Anyway, if anyone has further light to shed on this subject, please do so!  As I've said before we're all learning together.

No comments:

Post a Comment