Card Tricks

Posts Tagged ‘array’

See how you can use the non-Ctrl+Shift+Enter function AGGREGATE in place of SMALL in an array formula. Link for file: flightline.highline.edu

See how to count with multiple criteria! See how to do it with the new Excel 2007 COUNTIFS function and see how to do it in Excel 2003 with the SUM & IF functions in an Array formula (Don’t forget Ctrl + Shift + Enter). See that the new Excel 2007 COUNTIFS function is a much easier solution than was available in earlier versions of Excel. See how to get a count of the number of sales for a department AND a Product. Count values between Upper & Lower Value. Count values between an upper and lower bound. Count Values based on Two Conditions. Count Values based on two criteria. Count Values based on multiple conditions criteria. Count values between an upper and lower Limit.

See how to create a dynamic array formula quickly using the Drill Through Method so that the sub table will automatically be populated with any new data added to the Master Sheet. See the functions: COUNTIF, IF, ROWS, INDEX, SMALL, ROW. See how to create a 3 dimensional table set up like in a database program. Extract Records Formula

See how to create Boolean Logic formulas for AND and OR. See Logical Truth Tables for AND and OR. See how to create Array Formulas in three different ways: Array with SUM & IF functions (must use Ctrl + Shift + Enter) Array with SUMPRODUCT function (DON'T have to use Ctrl + Shift + Enter) Array with SUM function (must use Ctrl + Shift + Enter)

See an amazing Array Formula from Domenic and Ron Coderre at the Mr Excel Message Board that extracts only the numbers from a text string. See the functions LEN, INDIRECT, ROW, MID, ISNUMBER, INDEX, LARGE, SUMPRODUCT, and SUM.