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 @ http://www.mrexcel.com/forum/excel-questions/593233-sumif-criteria-array.html

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.

Formula for the Day: =Sum(number1, number2 . . .)

"=sum( )"

The sum function in Excel is wonderful!

1. In the formula bar, type "=sum("
2. Select your range (what are you adding?) - Click on a cell or multiple cells, or a whole row/column
3. Close the formula with ")".
4. Press Enter

This will add all of the numerical data within the cell range(s) that you provided.  It's very simple and much faster than having to type =A1+A2+A3...

*You can also add items from multiple spreadsheets! after you select your first cell reference to add, simply type a comma "," to indicate that the next item is a new reference.  Select the spreadsheet tab at the bottom, select the cell with the number you're looking to add and presto! You've added two cells on two different spreadsheets.  - This method is especially handy if you're creating a summary page to provide a quick glance at high level data.