Hi, Can anyone tell me the correct syntax for referencing two cells as a criterion in a formula. For example if I want to sum cells in b1:b5 if cells a1:a5 are greater than the value in c2 I would write the following: =sumif(a1:a5,">"&c2,b1:b5) If I want to sum b1:b5 if cells in a1:a5 are greater than the value in c2 and less than c3 what should I write? =sumif(a1:a5,and(">"&c2,"<"&c3),b1:b5) this doesn't work and neither does this.... =sumif(a1:a5,and(>c2,<c3),b1:b5). Any help gratefully received. Thanks, Will willcull@...

The formula below will return a GPA for our salespeople =IF(H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0)))), but I need to add one more criteria and that if another cell (C4) = 0 then the results of this formula need to be 0 as well. I hope this makes sense, can someone please help me. Thanks! Something like: =IF(C4=0,0,H7>0.9,4,IF(H7>=0.8,3,IF(H7>=0.7,2,IF(H7>=0.6,1,0))))) Regards, Fred "DMW" <DMW@discussions.microsoft.com> wrote in message news:1F81AAD9-C057-4504-8B01-8C1786BF0DD4@microsoft.com... > The formula below wi...

I have a column where each cell has a formula that constructs a string. How do I copy/paste this to an adjacent column such that the string acts as a formula in the new column's cells? For a simplified example, column B has the formulas =concatenate("=text(",a1,")") =concatenate("=text(",b1,")") =concatenate("=text(",c1,")") etc So that column B's values show =text(123) =text(456) =text(789) etc How do I copy/paste column B to column C so that column C shows the values of these literal formulas 123 (as text, in this ex...

Sample of what I am trying to achieve: Worksheet1: (column A, B, C, D, E) - source data id,primary region, secondary region, tertiary region, status 111,americas,new york, NA, increase 111,americas,canada,NA, increase 111,americas,mexico,LATAM,increase 111,americas,peru,LATAM,decrease 112,europe,france,WEST,increase 112,europe,spain,WEST,decrease 112,europe,uk,WEST,decrease 112,europe,portugal,WEST,decrease etc.... Worksheet2: (contains summary sheet) - summarize data 111,americas,NA, increase, 2 <----- count instances found from worksheet1 111,americas,LATAM,increase, 1 111,americas,LA...

This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C8FEBA.BDA26D90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: base64 SSBoYXZlIHNvbWUgcGFydCBudW1iZXJzIHRoYXQgSSBhbSBzZWFyY2hpbmcgZm9yIGV4dGVybmFs bHkgdXNpbmcgdGhlICJGYWxzZSIgcXVhbGlmaWVyLiAgU29tZSBmaWVsZHMgcmV0dXJuIGEgdmFs dWUgb2YgIiNOL0EiICBhcyB0aGVyZSBpcyBubyB2YWx1ZS4gIENhbiBhbnlvbmUgaGVscCBtZSB3 aXRoIGEgZm9ybXVsYSB0byByZXR1cm4gYSBmaXhlZCB2YWx1ZSAoaS5lLiAxLjApIGlmIHRoZSBm b3JtdWxhIHJldHVybnMgIiNOL0EiIGFzIGEgdmFsdWU/DQoNCg0KVEhhbmtzDQoNClBhdWw= ------=_NextPart_000_0008_01C8FEBA....

How do I write the formula mentioned below to encompass rows 5 - 778 so that it takes care of all at once and then of course this expans on a monthly basis both across columns as new months are included and then down rows as new clients are included. Thanks. select cell D2:X2 (so starting with the third month value) >- in the conditional format dialog enter the following formula: >=(C2<B2)*(D2<C2) >- choose a format Hi just select all these rows and enter the formula =(C5<B5)*(D5<C5) -- Regards Frank Kabel Frankfurt, Germany "corphealth" <anonymo...

Is it possible with Excel 2000 to automatically make a list of all the formulas you are using in your spreadsheet to print them. Thanks...Steve -- Steven E ----------------------------------------------------------------------- Steven E.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1512 View this thread: http://www.excelforum.com/showthread.php?threadid=26758 This subroutine makes a new sheet (Formulas in Sheen N) in which all formulas are listed together with cell reference and value. Not mine-own but I have lost the reference to the author but I thank h...

I have a concatenated formula that pulls text from 2 different cells. The 2 cells are formatted differently (i.e. 1 cell has blue text an the other cell has red text and a different font) and I want to kee these formats. However, the cell with the concatenated formula take the format of that cell. Is there anyway to do this -- andy281 ----------------------------------------------------------------------- andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596 View this thread: http://www.excelforum.com/showthread.php?threadid=27440 You men to have th...

( A ) ( B ) ( C ) ( D ) E ) ( F ) HUNTER-1 ( LARGE 20X24 ) 1 35.37 2.2 S PATTERN CHANGE HUNTER-1 ( LARGE 20X24 ) 1 35.37 1.65 20 CONVEYOR FULL HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.92 01 TURNTABLE HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.4 10 DRAW PINS HUNTER-1 ( LARGE 20X24 ) 1 35.37 0.48 22 DIST. CRANE Without Pattern Cahange 35.37 3.45 With Pattern Change 37.57 5.65 Pattern Change Only 37.57 2. -- vane032 ----------------------------------------------------------------------- vane0326's Profile: http://www.excelforum.com/member....

I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

I need to sum information based on several other criteria -- accoun number, sort codes, company names. The array formul {=SUM(A1:F25="A")*(E1:E25>0))} returns a count rather than a sum Does anyone know of a way get it to add my data rather than countin how many? Staci -- Message posted from http://www.ExcelForum.com Hi if you want to sum column E try the array formula =SUM((A1:F25="A")*(E1:E25>0)*(E1:E25)) or a non array formula =SUMPRODUCT((A1:F25="A")*(E1:E25>0),E1:E25) -- Regards Frank Kabel Frankfurt, Germany > I need to sum information ...

I have two customers with two different list of procedures on one report. cell A1 has a drop down list of the customers when a certain customer is chosen How do I write a formula so that when I choose Cutomer 1 only their list of procedures show up in a drop down box in my procedure area. See Debra's article on dynamic validation: http://www.contextures.com/xldataval02.html -- Best Regards, Luke M "Jane Griffel" <JaneGriffel@discussions.microsoft.com> wrote in message news:E2411A44-0F70-4DA1-91E8-DA9198C5F704@microsoft.com... >I have two customers wi...

Is there anyway to insert a formula into a comment box?? I have one sheet with three columns which contain defect code numbers. I have another worksheet tab in the same excel file which contains a list of defect code numbers and defect descriptions. I want to display the defect description in a yellow comment box when I move the cursor over the cell which contains the defect code number. I looked at Excel's comment feature, but it appears that these comment boxes can contain only fixed text. Is there any way to incorporate a lookup function into the comment box so that it can look up ...

Hello. I need a formula to do a vlookup for whenever a certain cost code/cost type appears in column A, then go to column F and sum the dollar amounts. Help please :) Hi, You can only use full columns in E2007 for E2003 and earlier define a shorter range =SUMPRODUCT((A:A="Your cost code")*(F:F)) Mike "Alberta Rose" wrote: > Hello. I need a formula to do a vlookup for whenever a certain cost > code/cost type appears in column A, then go to column F and sum the dollar > amounts. Help please :) 2003 use array formula { =SUM((A1:A10...

I have a workbook that is not shared or protected, and the formulas I enter do not calculate. If I enter =C2=B2 it displays the formula as typed instead of calculating. There is no apostrophe before the "=" sign either. What's the issue? How can I get my formulas to work? Thanks. EU Never mind. The format of the cell was set to "Text". I reset it to "General" and now the cell will allow the formula to calculate. Thanks. "Excel User" wrote: > I have a workbook that is not shared or protected, and the formulas I enter > do not calculate....

I am trying to copy formulas in a spreadsheet that pulls the data from every 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, n6). When I try to copy the pattern to the next 4 cells in the row, anticipating r6, v6, etc. instead it repeats as f6, j6, n6, r6. How do I correct this? One way =OFFSET(Sheet2!$B$6,,COLUMN(A:A)*4-4) copy across Regards, Peo Sjoblom "Tracey" wrote: > I am trying to copy formulas in a spreadsheet that pulls the data from every > 4th column in another spreadsheet in a consistent pattern (eg. b6, f6, j6, > n6). Wh...

Dear All, I am using Win XP, and Office XP, when I copy one excel file to other disk drive, a path of the source drive had been added to all formula in the cell in the target file, but this is not the way I wanted. How to prevent the automatic source added to the formula ? Thanks. Select the range to copy edit|Replace what: = (equal sign) with: $$$$$ replace all Now all the formulas are plain old text. Do the copy|paste Then fix the original range and the pasted range: Select the range edit|replace what: $$$$$ with: = replace all And the strings will be converted to formulas. andy wr...

I want to do something like this Sub Macro1() If Range("A1") =(=B1 + B2) Then ' This is a formula Exit Sub Else Range("C1").Select 'Has correct formula Selection.Copy Range("A1").Select Selection.PasteSpecial Paste:=xlFormulas End If End Sub How do I do this? Hi Try Sub Macro1() If Range("A1").HasFormula Then Exit Sub Else Range("C1").Copy Range("A1").PasteSpecial Paste:=xlFormulas Application.CutCopyMode = False ...

Hi If I have a two columns of data say, A and B and the are added together in rows in Column C eg A1 + B1 = C1 A2 + B2 = C2 etc If I then move the contents of cell A1 and put it in say A23 the value of C1 does not change because the C1 become A23 + B1. However, if I sort Column A then the value of C1 changes and the formula appears to be wrong because it now takes the new value of cell A1 How can I make sure that when the sort takes place that wherever the value in A1 ends up it is still added to B1 to create a value in C1? Any help appreciated Hi Andy, Why aren't you moving th...

I am trying to copy a cell from one file into another file in a macro. There are hundreds of files all linking to the same cell so I thought I would set the formula up so that is has a variable for the filename but I can't figure out the syntax. The formula should link to cell $r$16 in worksheet "monthly" in the changing file name and the variable I used for the changing file name is budget_file. The following didn't work. Can someone tell me how to do this? ActiveCell.FormulaR1C1 = "=" & budget_file & 'monthly'!$r$16&qu...

Hello I would like some help with a formula I require to speed up my analysi of areas. A B C D E F Colums Length Breadth Area M2 <50 50-500 >500 Headings 5 5 25 25 - - 25 25 625 625 20 20 400 400 I would like the areas to appear in categories <50, 50-500 and >500 bu I do not know the formula require. Please help as this would speed up my workload. Thanks Regards Jennif...

Hi! Can i make a formula that only shows the number of ex: "A" in a column and ignore any hidden rows that result from filtering. Maybe i ca combine =SUBTOTAL(3;P:P) and =COUNTIF($P:$P;"=A") in some way? Regards Gunnar Gunnar, You basically need to reproduce your filtering: =SUMPRODUCT((P1:P10="A")*(B1:B10="B")*(C1:C10="C")*1) HTH, Bernie MS Excel MVP "Gunnar Sandstr�m" <gunnar.sandstrom@ericsson.com> wrote in message news:c9n09c$hh2$1@newstree.wise.edt.ericsson.se... > Hi! > Can i make a formula that only shows ...

I want to copy a VLOOKUP formula down down the worksheet. When I use the drag handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one of the values but i want the other to stay the same. How do I do it?? Please help Mart A preceding $ indicates a part of the address to hold constant, as in $A1, A$1, or $A$1 to hold the column, row, or both constant. Jerry Martc wrote: > I want to copy a VLOOKUP formula down down the worksheet. When I use the drag > handle it increments 2 values ie A1, A2, A3 etc. I want it to increment one > of the values but i want the ...

Hi I have a workbook that has several formulas and 15 sheets which causes the workbook to be very large. Is there a way to reference to a formula instead of copy and pasting the same formula on every worksheet? thanks On Feb 17, 6:23=A0pm, "Fawn Lagimodiere" <flagimodi...@shaw.ca> wrote: > Hi I have a workbook that has several formulas and 15 sheets which causes > the workbook to be very large. =A0Is there a way to reference to a formul= a > instead of copy and pasting the same formula on every worksheet? > > thanks Group the sheets, select the first she...