Need to look up Multiple Criteria

Use a simple "&" instead of multiple IFs for the 2 columns of criteria and for the sumif formula

Combine the columns of data in a new column with an "&" (i.e. A1&B1) into C1
Then combine the criteria for the sumif using an "&"

Example: =SUMIF('FY07'!$Z:$Z,'FY07 Totals'!$B36&'FY07 Totals'!$C36,'FY07 '!L:L)
Example: =SUMIF('FY07'!$Z:$Z, Criteria 1 & Criteria 2 ,'FY07 '!L:L)

This shows that you can use a sumif and pull multiple criteria using an "&" in between the criteria field once that the data is combined. Nice trick

But there is an easier way: Arrays

Arrays allow you to look up multiple criteria with a " * " in between criteria using many equations

Example:
=SUM{((Business!$H$5:$H$1000="Not Yet Tested")*(Business!$J$5:$J$1000>B15)*(Business!$J$5:$J$1000<=B16))}

This says to count a cell if it meets 3 criteria. It uses the sum function b/c if a cell has text and it meets a true condition (i.e. all 3 criteria), it will automatically assign it the value of 1...thus, this function sums up all those ones. If the columns had actual numbers, it would sum those numbers)

There is a trick however; you must press CTRL SHFT ENTER after you create the formual and any time you enter the formula box...otherwise, it won't calculate

Will post more on arrays shortly

SUBTOTAL = POWERFUL FUNCTION

Use the Subtotal Pocketknife function to do 11 different tasks. Simply use =subtotal(#,list) and use the # 1-11 to delineate the task and select the cell range list. The function is completely multi-functional. What makes it powerful is combinining it with autofiltering to show different results. The subtotal function will only "subtotal" the selected filtered data. Incredible pocketknife function makes for an incredible excel day. Here are the 11 options:

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Favorite short-cuts with the ALT key

Sorry serious Excelers. It’s been a while since I posted. Attached are a few recent shortcuts I found that have helped:

ALT +E +D, then “Enter” to select “Delete row” - Will Delete a row wherever you selected a cell. ALT +E +D can be performed with the left hand quickly and this short-cut helps avoid the use of the mouse

ALT +I+R - Will Insert a row wherever you selected a cell

As a reminder, ALT + any menu first letter will select the menu category and other letters will perform some of the options listed within... or using the arrow keys to select inside the menu works as well

Adding Buttons that Perform Operations

1st step: Create a macro

Go to Tools; Macro; Record New Macro

First name the macro as the dialog indicates and then record whatever you want your macro button to do and go back to Tools; Macro; End Recording.  Tip: Make sure to ESC out of any highlighting that some functions so that the macro can capture the end desired state.

2nd step: Create your button

Go to View; Toolbars; Forms

Click the button icon and using the plus symbol, create the desired size of button on your worksheet.  As soon as you finish the desired length, a screen will pop up that allows you to select any of your macros to the button.  Select the macro you have created.  

You now have a button that will perform the desired operation.  Select Alt F11 if you want to dive into the VBA of the Macro if you need to modify anything in detail.

Piece of cake for quickly adding functionality to a spreadsheet, model, or other.  

Stay tuned.  Congrats to Reginold Baxter for being Excel Tricks Blog reader of the month.  

More Comprehensive List of Shortcuts

Keys for moving and scrolling in a worksheet or workbookArrow keys: Move one cell up, down, left, or right
CTRL+arrow: key Move to the edge of the current data regionHOME: Move to the beginning of the rowCTRL+HOME: Move to the beginning of the worksheetCTRL+END: Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottom-most used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1PAGE DOWN: Move down one screenPAGE UP: Move up one screenALT+PAGE DOWN: Move one screen to the rightALT+PAGE UP: Move one screen to the leftCTRL+PAGE DOWN: Move to the next sheet in the workbookCTRL+PAGE UP: Move to the previous sheet in the workbookCTRL+F6 or CTRL+TAB: Move to the next workbook or windowCTRL+SHIFT+F6 or CTRL+SHIFT+TAB: Move to the previous workbook or windowF6: Move to the next pane in a workbook that has been splitSHIFT+F6: Move to the previous pane in a workbook that has been splitCTRL+BACKSPACE: Scroll to display the active cellF5: Display the Go To dialog boxSHIFT+F5: Display the Find dialog boxSHIFT+F4: Repeat the last Find action (same as Find Next)TAB: Move between unlocked cells on a protected worksheet Keys for moving in a worksheet with End mode onEND: Turn End mode on or offEND, arrow key: Move by one block of data within a row or columnEND, HOME: Move to the last cell on the worksheet, which is the cell at the intersection of the rightmost used column and the bottom-most used row (in the lower-right corner), or the cell opposite the home cell, which is typically A1END, ENTER: Move to the last cell to the right in the current row that is not blank; unavailable if you have selected the Transition navigation keys check box on the Transition tab (Tools menu, Options command)Keys for moving in a worksheet with SCROLL LOCK onSCROLL LOCK: Turn SCROLL LOCK on or offHOME: Move to the cell in the upper-left corner of the windowEND: Move to the cell in the lower-right corner of the windowUP ARROW or DOWN ARROW: Scroll one row up or downLEFT ARROW or RIGHT ARROW: Scroll one column left or rightTip   When you use scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to preserve your selection while you scroll through the worksheet, turn on SCROLL LOCK first.Keys for previewing and printing a document
CTRL+P or CTRL+SHIFT+F12: Display the Print dialog box
Work in print previewArrow keys: Move around the page when zoomed inPAGE UP or PAGE DOWN: Move by one page when zoomed outCTRL+UP ARROW or CTRL+LEFT ARROW: Move to the first page when zoomed outCTRL+DOWN ARROW or CTRL+RIGHT ARROW: Move to the last page when zoomed out
Keys for working with worksheets, charts, and macrosSHIFT+F11 or ALT+SHIFT+F1: Insert a new worksheetF11 or ALT+F1: Create a chart that uses the current rangeALT+F8: Display the Macro dialog boxALT+F11: Display the Visual Basic EditorCTRL+F11: Insert a Microsoft Excel 4.0 macro sheetCTRL+PAGE DOWN: Move to the next sheet in the workbookCTRL+PAGE UP: Move to the previous sheet in the workbookSHIFT+CTRL+PAGE DOWN: Select the current and next sheet in the workbookSHIFT+CTRL+PAGE UP: Select the current and previous sheet in the workbook
Keys for entering data on a worksheet
ENTER: Complete a cell entry and move down in the selectionALT+ENTER: Start a new line in the same cellCTRL+ENTER: Fill the selected cell range with the current entrySHIFT+ENTER: Complete a cell entry and move up in the selectionTAB: Complete a cell entry and move to the right in the selectionSHIFT+TAB: Complete a cell entry and move to the left in the selectionESC: Cancel a cell entryBACKSPACE: Delete the character to the left of the insertion point, or delete the selectionDELETE: Delete the character to the right of the insertion point, or delete the selectionCTRL+DELETE: Delete text to the end of the lineArrow keys: Move one character up, down, left, or rightHOME: Move to the beginning of the lineF4 or CTRL+Y: Repeat the last actionSHIFT+F2: Edit a cell commentCTRL+SHIFT+F3: Create names from row and column labelsCTRL+D: Fill downCTRL+R: Fill to the rightCTRL+F3: Define a nameKeys for working in cells or the formula barBACKSPACE: Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit cell contentsENTER: Complete a cell entryCTRL+SHIFT+ENTER: Enter a formula as an array formulaESC: Cancel an entry in the cell or formula barCTRL+A: Display the Formula Palette after you type a function name in a formulaCTRL+SHIFT+A: Insert the argument names and parentheses for a function after you type a function name in a formulaCTRL+K: Insert a hyperlinkENTER (in a cell with a hyperlink): Activate a hyperlinkF2: Edit the active cell and position the insertion point at the end of the lineF3: Paste a defined name into a formulaSHIFT+F3: Paste a function into a formulaF9: Calculate all sheets in all open workbooksCTRL+ALT+F9: Calculate all sheets in the active workbookSHIFT+F9: Calculate the active worksheet= (equal sign): Start a formulaALT+= (equal sign): Insert the AutoSum formulaCTRL+; (semicolon): Enter the dateCTRL+SHIFT+: (colon): Enter the timeCTRL+SHIFT+" (quotation mark): Copy the value from the cell above the active cell into the cell or the formula barCTRL+` (single left quotation mark): Alternate between displaying cell values and displaying cell formulasCTRL+' (apostrophe): Copy a formula from the cell above the active cell into the cell or the formula barALT+DOWN ARROW: Display the AutoComplete list
Keys for formatting dataALT+' (apostrophe): Display the Style dialog boxCTRL+1: Display the Format Cells dialog boxCTRL+SHIFT+~: Apply the General number formatCTRL+SHIFT+$: Apply the Currency format with two decimal places (negative numbers appear in parentheses)CTRL+SHIFT+%: Apply the Percentage format with no decimal placesCTRL+SHIFT+^: Apply the Exponential number format with two decimal placesCTRL+SHIFT+#: Apply the Date format with the day, month, and yearCTRL+SHIFT+@: Apply the Time format with the hour and minute, and indicate A.M. or P.M.CTRL+SHIFT+!: Apply the Number format with two decimal places, thousands separator, and minus sign (-) for negative valuesCTRL+SHIFT+&: Apply the outline borderCTRL+SHIFT+_: Remove outline bordersCTRL+B: Apply or remove bold formattingCTRL+I: Apply or remove italic formattingCTRL+U: Apply or remove an underlineCTRL+5: Apply or remove strikethrough formattingCTRL+9: Hide rowsCTRL+SHIFT+( (opening parenthesis): Unhide rowsCTRL+0 (zero): Hide columnsCTRL+SHIFT+) (closing parenthesis): Unhide columns
Keys for editing dataF2: Edit the active cell and put the insertion point at the end of the lineESC: Cancel an entry in the cell or formula barBACKSPACE: Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contentsF3: Paste a defined name into a formulaENTER: Complete a cell entryCTRL+SHIFT+ENTER: Enter a formula as an array formulaCTRL+A: Display the Formula Palette after you type a function name in a formulaCTRL+SHIFT+A: Insert the argument names and parentheses for a function, after you type a function name in a formulaF7: Display the Spelling dialog boxKeys for inserting, deleting, and copying a selectionCTRL+C: Copy the selectionCTRL+X: Cut the selectionCTRL+V: Paste the selectionDELETE: Clear the contents of the selectionCTRL+HYPHEN: Delete the selectionCTRL+Z: Undo the last actionCTRL+SHIFT+PLUS SIGN: Insert blank cells
Keys for moving within a selectionENTER: Move from top to bottom within the selection (down), or move in the direction that is selected on the Edit tab (Tools menu, Options command)SHIFT+ENTER: Move from bottom to top within the selection (up), or move opposite to the direction that is selected on the Edit tab (Tools menu, Options command)TAB: Move from left to right within the selection, or move down one cell if only one column is selectedSHIFT+TAB: Move from right to left within the selection, or move up one cell if only one column is selectedCTRL+PERIOD: Move clockwise to the next corner of the selectionCTRL+ALT+RIGHT ARROW: Move to the right between nonadjacent selectionsCTRL+ALT+LEFT ARROW: Move to the left between nonadjacent selections
Keys for selecting cells, columns, or rows
CTRL+SHIFT+* (asterisk): Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)SHIFT+arrow key: Extend the selection by one cellCTRL+SHIFT+arrow key: Extend the selection to the last nonblank cell in the same column or row as the active cellSHIFT+HOME: Extend the selection to the beginning of the rowCTRL+SHIFT+HOME: Extend the selection to the beginning of the worksheetCTRL+SHIFT+END: Extend the selection to the last used cell on the worksheet (lower-right corner)CTRL+SPACEBAR: Select the entire columnSHIFT+SPACEBAR: Select the entire rowCTRL+A: Select the entire worksheetSHIFT+BACKSPACE: Select only the active cell when multiple cells are selectedSHIFT+PAGE DOWN: Extend the selection down one screenSHIFT+PAGE UP: Extend the selection up one screenCTRL+SHIFT+SPACEBAR: With an object selected, select all objects on a sheetCTRL+6: Alternate between hiding objects, displaying objects, and displaying placeholders for objectsCTRL+7: Show or hide the Standard toolbarF8: Turn on extending a selection by using the arrow keysSHIFT+F8: Add another range of cells to the selection; or use the arrow keys to move to the start of the range you want to add, and then press F8 and the arrow keys to select the next rangeSCROLL LOCK, SHIFT+HOME: Extend the selection to the cell in the upper-left corner of the windowSCROLL LOCK, SHIFT+END: Extend the selection to the cell in the lower-right corner of the window
Tip   When you use the scrolling keys (such as PAGE UP and PAGE DOWN) with SCROLL LOCK turned off, your selection moves the distance you scroll. If you want to keep the same selection as you scroll, turn on SCROLL LOCK first.Keys for extending the selection with End mode onEND: Turn End mode on or offEND, SHIFT+arrow key: Extend the selection to the last nonblank cell in the same column or row as the active cellEND, SHIFT+HOME: Extend the selection to the last cell used on the worksheet (lower-right corner)END, SHIFT+ENTER: Extend the selection to the last cell in the current row. This keystroke is unavailable if you selected the Transition navigation keys check box on the Transition tab (Tools menu, Options command).Keys for selecting cells that have special characteristicsCTRL+SHIFT+* (asterisk): Select the current region around the active cell (the current region is a data area enclosed by blank rows and blank columns)CTRL+/: Select the current array, which is the array that the active cell belongs toCTRL+SHIFT+O (the letter O): Select all cells with commentsCTRL+\: Select cells in a row that don't match the value in the active cell in that row. You must select the row starting with the active cell.CTRL+SHIFT+|: Select cells in a column that don't match the value in the active cell in that column. You must select the column starting with the active cell.CTRL+[ (opening bracket): Select only cells that are directly referred to by formulas in the selectionCTRL+SHIFT+{ (opening brace): Select all cells that are directly or indirectly referred to by formulas in the selectionCTRL+] (closing bracket): Select only cells with formulas that refer directly to the active cellCTRL+SHIFT+} (closing brace): Select all cells with formulas that refer directly or indirectly to the active cellALT+; (semicolon): Select only visible cells in the current selection
Keys for selecting a chart sheetCTRL+PAGE DOWN: Select the next sheet in the workbook, until the chart sheet you want is selectedCTRL+PAGE UP: Select the previous sheet in the workbook, until the chart sheet you want is selected
Keys for selecting an embedded chart
Note   The Drawing toolbar must already be displayed.Press F10 to make the menu bar active.Press CTRL+TAB or CTRL+SHIFT+TAB to select the Drawing toolbar.Press the RIGHT ARROW key to select the Select Objects  button on the Drawing toolbar.Press CTRL+ENTER to select the first object.Press the TAB key to cycle forward (or SHIFT+TAB to cycle backward) through the objects until sizing handles appear on the embedded chart you want to select.Press CTRL+ENTER to make the chart active.
Keys for selecting chart itemsDOWN ARROW: Select the previous group of itemsUP ARROW: Select the next group of itemsRIGHT ARROW: Select the next item within the groupLEFT ARROW: Select the previous item within the groupKeys for working with a data formALT+key, where key is the underlined letter in the field or command name: Select a field or a command buttonDOWN ARROW: Move to the same field in the next recordUP ARROW: Move to the same field in the previous recordTAB: Move to the next field you can edit in the recordSHIFT+TAB: Move to the previous field you can edit in the recordENTER: Move to the first field in the next recordSHIFT+ENTER: Move to the first field in the previous recordPAGE DOWN: Move to the same field 10 records forwardCTRL+PAGE DOWN: Move to a new recordPAGE UP: Move to the same field 10 records backCTRL+PAGE UP: Move to the first recordHOME or END: Move to the beginning or end of a fieldSHIFT+END: Extend a selection to the end of a fieldSHIFT+HOME: Extend a selection to the beginning of a fieldLEFT ARROW or RIGHT ARROW: Move one character left or right within a fieldSHIFT+LEFT ARROW: Select the character to the leftSHIFT+RIGHT ARROW: Select the character to the right
Keys for using AutoFilterArrow keys to select the cell that contains the column label, and then press ALT+DOWN ARROW: Display the AutoFilter list for the current columnDOWN ARROW: Select the next item in the AutoFilter listUP ARROW: Select the previous item in the AutoFilter listALT+UP ARROW: Close the AutoFilter list for the current columnHOME: Select the first item (All) in the AutoFilter listEND: Select the last item in the AutoFilter listENTER: Filter the list by using the selected item in the AutoFilter list
Keys for outlining dataALT+SHIFT+RIGHT ARROW: Group rows or columnsALT+SHIFT+LEFT ARROW: Ungroup rows or columnsCTRL+8: Display or hide outline symbolsCTRL+9: Hide selected rowsCTRL+SHIFT+( (opening parenthesis): Unhide selected rowsCTRL+0 (zero): Hide selected columnsCTRL+SHIFT+) (closing parenthesis): Unhide selected columns
Keys for the PivotTable and PivotChart WizardUP ARROW or DOWN ARROW: Select the previous or next field button in the listLEFT ARROW or RIGHT ARROW: Select the field button to the left or right in a multicolumn field button listALT+C: Move the selected field into the Column areaALT+D: Move the selected field into the Data areaALT+L: Display the PivotTable Field dialog boxALT+P: Move the selected field into the Page areaALT+R: Move the selected field into the Row area
Keys for page fields in a PivotTable or PivotChart reportCTRL+SHIFT+* (asterisk): Select the entire PivotTable reportArrow keys to select the cell that contains the field, and then ALT+DOWN ARROW: Display the list for the current field in a PivotTable reportArrow keys to select the page field in a PivotChart report, and then ALT+DOWN ARROW: Display the list for the current page field in a PivotChart reportUP ARROW: Select the previous item in the listDOWN ARROW: Select the next item in the listHOME: Select the first visible item in the listEND: Select the last visible item in the listENTER: Display the selected itemSPACEBAR: Select or clear a check box in the listKeys for laying out a PivotTable or PivotChart reportPress F10 to make the menu bar active.Press CTRL+TAB or CTRL+SHIFT+TAB to select the PivotTable toolbar.Press the LEFT ARROW or RIGHT ARROW key to select the menu to the left or right or, when a submenu is visible, to switch between the main menu and submenu.Press ENTER (on a field button) and the DOWN ARROW and UP ARROW keys to select the area you want to move the selected field to.
Note   To scroll to the top or bottom of the field list, press ENTER on the More Fields  or  button.Keys for grouping and ungrouping PivotTable itemsALT+SHIFT+RIGHT ARROW: Group selected PivotTable itemsALT+SHIFT+LEFT ARROW: Ungroup selected PivotTable itemsKeys to use with the OLAP Cube WizardUse these keys to complete Step 2 in the OLAP Cube Wizard.To move a field from the Source fields list to the Dimensions boxPress the TAB key to select the Source fields list.Press the UP ARROW or DOWN ARROW key to select the field you want to move.Because you cannot change the order of a field after you move it, select first the field you want to appear at the top of the Dimensions box.Press the TAB key to select the > button, and then press ENTER.To move other fields, repeat steps 1 through 3.To move a field to a lower or higher level in the Dimensions boxPress the TAB key to select the Dimensions box.Press the UP ARROW or DOWN ARROW key to select the field you want to move.Press CTRL+X.Press the UP ARROW or DOWN ARROW key to select where you want to move the field.Press CTRL+V.

Source: http://www.jethromanagement.biz/ex_cuts.htm

Trouble with Dates in Excel

Trouble with Dates in Excel that have mysteriously shifted by 4 years?

Since I have not yet chatted with Bill to discuss this latest problem with Excel and remind him to get his act together, here is some info and a fix.  Excel has two ways to recognize dates.  One is from the 1904 system and one is the 1900 system.  Mac computers use the 1904 date system by default and Windows based PC’s use the 1900 system by default.  Thus, there can be issues when sharing Excel documents between Mac users and Windows users.  The 1900 system converts dates to serial numbers since the date Jan. 1st,1900 and the 1904 system converts dates to serial numbers since Jan. 1st, 1904.  Thus, depending on the version of excel, you can have two different serial numbers that may be translated incorrectly when sharing files.  This occurred recently when a Mac wizard sent me a file that I opened on my windows laptop.  It changed the dates by a difference of exactly 1,462 days.

How do I fix the date problem?

1. Tools/Edit menu, then click Options/Preferences, then click the Calculation tab.  
2. For the 1900 date system in the workbook, click to clear the 1904 Date System check box. To use the 1904 date system in the workbook, click to select the check box.  
3. Click OK.  

To give proper credit, I was given this tip by Justin from the Mango company, an expert Mac user.  Additionally, comprehensive information about the date systems can be found here at Microsoft support:  http://support.microsoft.com/kb/q180162/

The POWER of the CTRL key !!!!!!!!!!!!

Practicing the CTRL SHIFT shortcuts will save tons of time:

CTRL SHIFT :           Enters the time
CTRL SHIFT &                      Creates an outline box around whatever you select
CTRL SHIFT _          Removes the outline box around whatever has an outline
CTRL SHIFT *           Highlights all the text surrounding the cell filled with data or numbers
CTRL SHIFT !          Applies the number format to selected cells
CTRL SHIFT %          Applies the percentage format to the selected cells
CTRL SHIFT #          Applies the date format to the selected cells
CTRL SHIFT @          Applies the time format to the selected cells
CTRL SHIFT (          Unhide columns that are selected
CTRL SHIFT )          Unhide rows that are selected

The best way to practice these is to simply type some numbers into a cell and then to press CTRL SHIFT plus the keys 1 – 10…since every number plus SHIFT (which is already pressed) will change it to the symbol, you can sample every trick quickly in this way.  

Here are a few other CTRL shortcuts that can save time:

CTRL ;                Enters the date
CTRL 9               Hide Rows selected
CTRL 0               Hide Columns selected
CTRL D          Fills down current formula into all adjacent cells; this avoids copying first
CTRL K          Insert a hyperlink

If you use the group and outline function often, these are invaluable:

ALT SHIFT RIGHTARROW     Group rows or columnsALT SHIFT LEFTARROW       Ungroup rows or columns