eureka Wrote: ------------------------------------------------------- > BTW, unless you have 07 it is SUM(IF(…NOT SUMIF. > Whenever you use SUM(IF you have to put in in > an array, or it will not look at each piece of > data, just the first. > See above, I meant SUMIF. No 2007 for me. I’ve sadly been exposed to a sufficient level of geekery that on one project the absence of the MULTIPLYIF function was worked around by taking logs, then using SUMIF, but such people should get out more…
TheBigBean Wrote: ------------------------------------------------------- > cjones65 Wrote: > -------------------------------------------------- > ----- > > TheBigBean Wrote: > > > -------------------------------------------------- > > > > More like the array formula, > > {YOUREAMORON(IF(you=think sumif can replace > lookup > > functions because they serve completely > different > > purposes,IF(your name ryhmes with ‘the wig > > cream’,1,0)),0))} > > > Perhaps I’m a fool, but it is what I do for a job, > so I’m fairly confident. I’m not sure your > response really justifies a reply, but in any > case… > > For example, try > =SUMIF(RangeOfDates,DateYouWant,CorrespondingValue > Range). That’s equivalent, assuming you are > returning a numerical value, to > INDEX(CorrespondingValueRange,MATCH(DateYouWant,Ra > ngeOfDates,0)) which is equivalent to some > unauditable OFFSET MATCH combination that you > might use, and superior to LOOKUP variations as > you don’t need to repeat the range of dates all > the time. It is also more stable in the opinion of > many… It’s actually more work to use your formula because you need to constantly adjust the ranges as you add new data. plus you can only filter on one condition. and why would using sumif to lookup if you want your spreadsheet to be easily auditable? If I see sumif I am assuming you want to, you know, sum things, not look something up. Oh and your index function isn’t equivalent to the sumif example, it returns the location of the value you want not the actual value.
If you can learn this list and move back and forth and around, I think it is good. I’m a lot quicker than I used to be. . . Switch between Worksheets CTRL-PageUp/CTRL-PageDown Switch between Workbooks CTRL-Tab Move one character up, down, left, or right. Arrow keys Go to end of a contiguous range CTRL-Arrow Keys Select a cell range SHIFT+Arrow keys Highlight a contiguous range SHIFT-CTRL-Arrow Keys Select entire worksheet CTRL+A Move to the beginning of the line. HOME Go To F5 Move a Sheet/Copy a Sheet Alt-E-M Change Zoom Sizing Alt-V-Z Complete a cell entry and select… …the cell below. ENTER …the previous cell above. SHIFT+ENTER …the next cell to the right. TAB …the previous cell to the left. SHIFT+TAB Delete cell and then get inside the cell BACKSPACE Delete cell/selection. DELETE Edit inside a cell (edit cell mode) F2 Once inside edit cell mode (F2)… …Start a new line in the same cell. ALT+ENTER …Highlight individual characters within cells SHIFT+Arrow keys …Highlight contiguous string within cells SHIFT+CTRL+Arrow keys …Delete the preceding character. BACKSPACE …Delete the character to the right of the insertion point. DELETE …Cancel a cell entry. ESC Spell Check. F7 Insert a comment. SHIFT+F2 Fill down. CTRL+D Fill to the right. CTRL+R Undo the last action. CTRL+Z Redo the last action. F4 or CTRL+Y Hide the selected rows. CTRL+9 Unhide any hidden rows within the selection. CTRL+SHIFT+( (opening parenthesis) Hide the selected columns. CTRL+0 (zero) Unhide any hidden columns within the selection. CTRL+SHIFT+) (closing parenthesis) Highlight Entire Row SHIFT+SPACEBAR Highlight Entire Column CTRL+SPACEBAR Group Rows or Columns SHIFT+ALT+RIGHT ARROW KEY Ungroup Rows or Columns SHIFT+ALT+LEFT ARROW KEY Clear the contents of the selected cells. DELETE Delete the selected cells. CTRL+MINUS SIGN Insert blank cells. CTRL+SHIFT+PLUS SIGN Copy the selected cells. CTRL+C Cut the selected cells. CTRL+X Paste copied cells. CTRL+V Copy, then Paste Special Formats CTRL+C, Alt-E+S+T Copy, then Paste Special Formulas CTRL+C, Alt-E+S+F Display the Style dialog box. ALT+’ (apostrophe) Display the Format Cells dialog box. CTRL+1 Applies the general format CTRL+SHIFT+~ Number format: 2 decimal places, thousands separator, and minus sign (–) for negative values. CTRL+SHIFT+! Currency format: 2 decimal places (negative numbers in parentheses). CTRL+SHIFT+$ Percentage format with no decimal places. CTRL+SHIFT+% Exponential number format with two decimal places. CTRL+SHIFT+^ Date format with the day, month, and year. CTRL+SHIFT+# Time format with the hour and minute, and AM or PM. CTRL+SHIFT+@ Boldface CTRL+B Italicize CTRL+I Underline CTRL+U Strikethrough CTRL+5 Apply the outline border to the selected cells. CTRL+SHIFT+& Remove the outline border from the selected cells. CTRL+SHIFT+_ Start a formula. = (equal sign) In a formula, display the Insert Function dialog box. SHIFT+F3 Insert an AutoSum formula with the SUM function. ALT+= (equal sign) Copy the value from the cell above the active cell into the cell or the Formula Bar. CTRL+SHIFT+" (quotation mark) Copies a formula from the cell above the active cell into the cell or the Formula Bar. CTRL+’ (apostrophe) Alternate between displaying cell values and displaying formulas. CTRL+` (single left quotation mark) Recalculate all worksheets in all open workbooks. F9 Anchoring “Fixing” Cells F4 - must be in edit cell mode (F2) Go to precedent cell(s) CTRL-[ Highlight dependent cells F2 Auditing Toolbar (Go to precedent and dependent cells) Alt-T-U Press F2 to edit the cell, turn on NUM LOCK, and then press the following keys by using the numeric key pad: Cent ¢. ALT+0162 Pound sterling £. ALT+0163 Yen ¥. ALT+0165 Euro €. ALT+0128 Name a cell. CTRL+F3 Insert a hyperlink CTRL+K Activate a hyperlink. ENTER (in a cell with a hyperlink) Enter the date. CTRL+; (semicolon) Enter the time. CTRL+SHIFT+: (colon) Display a drop-down list of the values in the current column of a list ALT+DOWN ARROW
^ Probably the most helpful post on this thread
KrukVT Wrote: ------------------------------------------------------- > It’s actually more work to use your formula > because you need to constantly adjust the ranges > as you add new data. plus you can only filter on > one condition. and why would using sumif to > lookup if you want your spreadsheet to be easily > auditable? If I see sumif I am assuming you want > to, you know, sum things, not look something up. > > > Oh and your index function isn’t equivalent to the > sumif example, it returns the location of the > value you want not the actual value. Happy to offer help and tips, but no energy to argue.
- learn how to use the keyboard like you’re playing a piano 2. if something seems like it’s too manual, it is. google what you’re trying to do and commit the answer to memory because you’ll probably use it again. techonthenet.com usually gives pretty good answers
I’ve used VBA extensively with excel, building consulting tools, so if any one of you want any help with VBA, I could help you out. I could use the experience too, because I haven’t done any finance related work with VBA