Is there a function in VB or Excel that compares a column range with a certain value and returns a sum?
830543
{=SUM(IF($A$2:$A$11 = DATEVALUE("11/29/06"), $B$2:$B$11, 0))}\n\nThis example (an array formula, so hit CTRL+Shift+Enter when you finish typing it instead of just Enter (don't type the brackets - { } )) sums the contents of Column B  for every instance the Column A contains 11-29). Just modify it to match your cell references and it should work just fine.
140808
Try this...\nINSERT INTO #temp1 \nselect * \nfrom abc\nwhere\n( select x,y,z,\n  sum(case when a1 = z1 \nthen cast(a1 as float)* (-1) \nelse(cast a1 as float)) as Units\nfrom\nxyz\ngroup by \nx,y,z\nsum(case when a1 = z1 \nthen cast(a1 as float)* (-1) \nelse(cast a1 as float))>0\norder by \nx,y\n)
26787
If you're talking about Excel from Microsoft, heres a couple pointers\n\n1) every formula starts with an equals sign '='\n2) there are countless formulas you can use, your best bet would be to click on the '=' in the upper left of the window and a box will pop up to help you.
699216
1) In a Word table, you can insert a formula.  Try it with a column of numbers.  Move cursor to the last cell, click TABLE, then choose FORMULA.  Choose SUM(Above).  This will add all the cell values.  The formula will look like this: {=SUM(ABOVE)}\n\n2) Using same WORD table, copy the numbers to a different column and try inserting a new formula.  This time choose a formula from the formula list.  I recommend AVERAGE.  Then the formula looks like this {AVERAGE(BELOW)}.\n\nTo view the formulae described above, highlight the result cell, right click, choose toggle field codes.  Repeat to return it to result.\n\nFor auto-text:\n\n1) Click INSERT, choose, AutoText, then choose which you want to use.  I presume that page numbering is probably most commonly used.  Best to insert it in header or footer.\n\n2) Try a reference.  Click on INSERT, choose Cross-Reference.  In order to use this feature you need to have some styles in use that re numbered.  They will appear in the cross-reference box for you to choose from.\n\n\nHope these help,\n\nKind Regards,\nQwertyKPH @ Yahoo!
943817
[EDIT - DELETED A WHOLE BUNCH OF TEXT]\n\nthat kind of thing is complex to create in a macro form, normally the kind of thing that you'd pay someone to do because i'd imagine it's easily a couple of hours work to write all the code, test it, iron out bugs etc. there *may* be a built in function in excel to do something like this but i wouldn't have thought so - i'm happy to be proved wrong:\n\n----found on t'internet----\nA1:A10 (your list of values)\nB1:B10 (leave blank)\nC1: =A1*B1 (copy thist fomula down through to C10)\nC11: =SUM(C1:C10)\n\nNow to use Solver....\nTools>Solver\nSet Cell: C11\nEqual to the Value of: 1173.76\nBy Changing Cells: B1:B10\nSubject to the Constraints....\n(click the add button and constrain B1:B10 to Binary)\nClick [OK]\nClick [Solve]\n\nExcel will toggle cells B1:B10 between 1 and 0 until it comes up with a combination that sums to 1,173.76\n----end of instructions----\n\nit depends on the setup of your spreadsheet - if it's one huge page that's a jumble of undistinguishable invoices with no reference to who the invoices belong to, it means manually checking the results of the macro for the set of results that applies only to one 'customer'. ideally you'd want a seperate sheet for each customer and then the macro or above method can be ran on whichever sheet/customer the cheque is from.\n\ni dunno about other people on here but i wouldn't spend hours of my time creating such a thing for the sake of 10 points which aren't really worth anything - i CAN tell you that if the above method doesn't do the job that it'd mean creating a loop that will add up every possible combination of totals from the sheet then comparing it each time against the grand total until it gets a 'hit'.\n\ni can't imagine this kind of sorting being practical for huge amounts of figures, probably a list of 20 at most because you could get into some big big processing time otherwise.\n\ni would be interested in hearing anyone elses ideas about doing it?
661261
You could use conditional formatting on the cells where you are entering your data.  But you actually don't need to do conditional formatting, it's just that you can get smoother more interesting effects if you do.  An easier way might be a simple formula.\n\nIt sounds like you are entering data that uses Case numbers, but if you have already entered a case, you don't want to do it again.  Obviously you could just search the case number column to see if it already exists and if so, then skip entering it.  But a more automated way would be easier.\n\nThe essence of what you need to do is use a formula to tell you if what you just entered already exists in the rows above. (To use your example, does case 24 exist already in the rows above A27?)\n\nYou could insert a (temporary) column to the left of what you are typing in.  In that column you would enter a formula to count the number of instances where the case number you just entered already exists above.  Usually the result would be 1, sometimes 2.\n\nLets say you are on row 27 and your case numbers are in column B, and your counting column is column A.  Lets assume your data starts on row 2 at the top of the spreadsheet.  Your formula for cell A27 would be:\n=IF(COUNTIF($B$2:B27,B27)>1, "Exists Already!", "")\n\nNotice the $ signs in the Countif formula.  The formula range will adjust as appropriately as you copy it around.  You want to get this formula on every line next to every case number so that it can alert you if it seen there is more than once instance.\n\n(Actually the Data Validation mentioned below is a better solution because it will prohibit you from entering the data.  But it's all the same principle. Just more elegant because you don't need to insert a new column.)
404712
You can do this by VLOOKUP\n\nSheet 1 contains the full list \nSheet 2 your item codes\n\nIn Sheet 2.\nB2=VLOOKUP(A2,Sheet1!A1:C3,2)\n\nThis looks up the value in A2 in the range A1:C3, and returns the second column (B).
844650
I am putting some example:\nThe formula is actually put in the fields below relults so remove the " to operate.\n\nItemRateQtyResultFormula\nBread1510150"+ d2 * c2"\nBread 22510250"+ d3 * c3"\n\nTotal400"=sum(e2:e3)"
366534
It completely depends on where you are using this formula, is it in Excel? A program? What language?\n\nWe need more info.
65173
Paste this formula in the third cloumn\n\n=VLOOKUP(A1,B1:B4,1,0)\n\nAll the matching cells will get their values printed whereas the unmatched cells will contain #NA
1163223
Is there is no SUB command similar to SUM, my suggestion would be to make a second column, the same size as the one you are trying to subtract.  You can put it off screen, or so, then no one can see it.\n\nFor this example, we will assume you have the column A with the values you want to subtract.  We will also assume that column B is the new second column.  Both columns data starts at 1, ie, A1, and B1.\n\nNow, the cell B1 should have the formula '=A1' to have the same start value.\nThe cell B2 and onwards should have the formula: '=-A2', '=-A3', etc.  This is 'equals minus An'.\n\nThe get the total, have the formula as '=SUM(B1 : Bend)'\n\n[Edit]\nWhere Bend is the last row in column B, eg, B4.\n[/Edit]
668871
You may have to write that fomula out manually. \n \n=OR(AND(A1,NOT(B1)), AND(NOT(A1),B1))
653602
Do you have Excel, if you do, then Excel can do that.  I think the formulas are self explanatory but if it isn't obvious to you, a Google search should turn up on how to do this in Excel.
897335
You can do this in two ways:\nOne: Using Macro.\nTwo: Using functions\n\nI prefer using the option Two as a start.\nNow, I need you to focus with me...\n\n- Enter your two values "Default" and "1000" in two cells (Say Y1:Y2)\n- Create a Combo Box using "Forms Toolbar", that is go to View > Toolbars > Forms\n- The toolbar will show and you can create a combo box from it.\n- Right click on that combo box and go to Properties > Control, select two cells that have your values (Default and '1000') Y1:Y2 as the Input range, select a cell to link this combo box to (Say Z1).\n- Now click ok\n- Where ever you select a value from this combo box, Z1 cell will change to 1 or 2.\n- Your A1:E50 cells need to have this function, need to be changed. I have a question here (Do these cells have a numbers (A) or coming from a formulas (B) ?)\nA) If the A1:E50 cells have all constant numbers, I prefer you choose another range to put this formula in.\nSay G1:K50, put in G1 this formula and copy and paste to the others:\n= CHOOSE( $Z$1, A1, A1/1000)\nB) If they have a formula, just change it to this formula:\n= CHOOSE( $Z$1, [your old formula], [your old formula] /1000)\ndon't forget to change the value [your old formula] to your old formula on each cell.\n\nNeed more help? or would like to see the Macro section?\nmail me here\n\nEnjoy my profile, I am VBAXLMan
99226
http://www.functionx.com/vb/index.htm\nvisit this site, i hope it can help you. good luck
995900
It depends on how you have your other amounts organised.\n\nIf you have a column or row that is for periodic end amounts, eg, weekly totals.  You can set up a range for these.  For an example, assume the column A holds the weekly totals.   They start from A1 and finish A52.\n\nCreate a new cell and type this formula, without the quotes:\n'=sum(A1 : A52)'\nThats it.
101484
Take a look at the counta() command and combine it with addition in an if() formula.