Formula to find average of field for all rows that contain another field #2
Say I have a worksheet with the following information: Name Position Salary John Producer $10,000 Jeremy Producer $98,000 Jaime Producer $50,000 Darren Artist $67,000 Chris Artist $75,000 Clint Artist $30,000 Adam Artist $57,000 In Cell C2 (salary for John) I want to create a formula that looks for all rows that contain the same position as in B2 (John's position which is producer) and then calculates the average of all the salaries in column C of those rows that have the position producer. Then I want to compare that calculated average salary for all rows that have the position producer to the value in cell C2. I am using cell C2 as an example but I would like to do the same calculation for all salary rows. Basically I want to use conditional formatting to show if the average salary for that persons position is below, around, or above that persons salary. So once I create the conditional formatting formula I can copy it to all the cells that have salary. Can the above be done without using VBA or is VBA the only way to do it? If VBA is the only way to do it can someone point me in the rough direction of how I would go about it? Thank in advance for any help provided. - John --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0612-4, 03/25/2006 Tested on: 3/25/2006 12:13:46 PM avast! - copyright (c) 1988-2005 ALWIL Software. http://www.avast.com
joja_15
excel
39880 articles. 
0 followers.
7 Replies
158 Views
You can use CF with a formula of =$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) for lower. Higher is obvious, but what is the definition of around? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "John" <joja_15@yahoo.com> wrote in message news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... > Say I have a worksheet with the following information: > > Name Position Salary > John Producer $10,000 > Jeremy Producer $98,000 > Jaime Producer $50,000 > Darren Artist $67,000 > Chris Artist $75,000 > Clint Artist $30,000 > Adam Artist $57,000 > > > In Cell C2 (salary for John) I want to create a formula that looks for > all rows that contain the same position as in B2 (John's position which > is producer) and then calculates the average of all the salaries in > column C of those rows that have the position producer. Then I want to > compare that calculated average salary for all rows that have the > position producer to the value in cell C2. I am using cell C2 as an > example but I would like to do the same calculation for all salary > rows. Basically I want to use conditional formatting to show if the > average salary for that persons position is below, around, or above > that persons salary. So once I create the conditional formatting formula > I can copy it to all the cells that have salary. > > > Can the above be done without using VBA or is VBA the only way to do > it? If VBA is the only way to do it can someone point me in the rough > direction of how I would go about it? > > > Thank in advance for any help provided. > > > - John > > > > > --- > avast! Antivirus: Outbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 12:13:46 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > >
bob.phillips1Select all the cells you want to be highlighted by conditional formatting, with the active cell being in row 2. Then do Format=>Conditional Formatting change Cell Value is to Formula is. for the first condition use =abs($D2-(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)))>2000 click the format button and select the pattern you want click the add button change Cell Value is to Formula is. =$D2>(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) select formatting click the add button change Cell Value is to Formula is. =$D2<(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) select formatting -- Regards, Tom Ogilvy "John" <joja_15@yahoo.com> wrote in message news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... > Say I have a worksheet with the following information: > > Name Position Salary > John Producer $10,000 > Jeremy Producer $98,000 > Jaime Producer $50,000 > Darren Artist $67,000 > Chris Artist $75,000 > Clint Artist $30,000 > Adam Artist $57,000 > > > In Cell C2 (salary for John) I want to create a formula that looks for > all rows that contain the same position as in B2 (John's position which > is producer) and then calculates the average of all the salaries in > column C of those rows that have the position producer. Then I want to > compare that calculated average salary for all rows that have the > position producer to the value in cell C2. I am using cell C2 as an > example but I would like to do the same calculation for all salary > rows. Basically I want to use conditional formatting to show if the > average salary for that persons position is below, around, or above > that persons salary. So once I create the conditional formatting formula > I can copy it to all the cells that have salary. > > > Can the above be done without using VBA or is VBA the only way to do > it? If VBA is the only way to do it can someone point me in the rough > direction of how I would go about it? > > > Thank in advance for any help provided. > > > - John > > > > > --- > avast! Antivirus: Outbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 12:13:46 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > >
twogilvyIf your data covers a lot of rows, I would steer away from the use of Average as it will slow your calculations down. Test it for yourself to see if there is a difference or I am wrong. -- Regards, Tom Ogilvy "Tom Ogilvy" <twogilvy@msn.com> wrote in message news:u%23uQQ$DUGHA.1672@tk2msftngp13.phx.gbl... > Select all the cells you want to be highlighted by conditional formatting, > with the active cell being in row 2. > > Then do Format=>Conditional Formatting > > change Cell Value is to Formula is. > for the first condition use > =abs($D2-(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)))>2000 > click the format button and select the pattern you want > > click the add button > change Cell Value is to Formula is. > > =$D2>(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) > select formatting > > click the add button > change Cell Value is to Formula is. > =$D2<(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) > select formatting > > -- > Regards, > Tom Ogilvy > > > "John" <joja_15@yahoo.com> wrote in message > news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... > > Say I have a worksheet with the following information: > > > > Name Position Salary > > John Producer $10,000 > > Jeremy Producer $98,000 > > Jaime Producer $50,000 > > Darren Artist $67,000 > > Chris Artist $75,000 > > Clint Artist $30,000 > > Adam Artist $57,000 > > > > > > In Cell C2 (salary for John) I want to create a formula that looks for > > all rows that contain the same position as in B2 (John's position which > > is producer) and then calculates the average of all the salaries in > > column C of those rows that have the position producer. Then I want to > > compare that calculated average salary for all rows that have the > > position producer to the value in cell C2. I am using cell C2 as an > > example but I would like to do the same calculation for all salary > > rows. Basically I want to use conditional formatting to show if the > > average salary for that persons position is below, around, or above > > that persons salary. So once I create the conditional formatting formula > > I can copy it to all the cells that have salary. > > > > > > Can the above be done without using VBA or is VBA the only way to do > > it? If VBA is the only way to do it can someone point me in the rough > > direction of how I would go about it? > > > > > > Thank in advance for any help provided. > > > > > > - John > > > > > > > > > > --- > > avast! Antivirus: Outbound message clean. > > Virus Database (VPS): 0612-4, 03/25/2006 > > Tested on: 3/25/2006 12:13:46 PM > > avast! - copyright (c) 1988-2005 ALWIL Software. > > http://www.avast.com > > > > > > > >
Bob, thank you for the quick response! I am getting weird results with that formula. To simplify and debug it I just took the AVERAGE which was the following: =AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) The above formula returns $55,286 which is the average for all salaries not just the "Producer" ones. I verified that by doing: =AVERAGE(C2:C8). The actual value is supposed to be $52,667 which I verified by doing: =AVERAGE(C2:C4). Now here is the weird thing. If I select the formula in the text entry bar, click on the fx button at the bottom of the "Function Arguments" dialogue box it says "Formula result = $52,667". So that formula returns the correct result in the Function Arguments dialogue box but it does not return the correct result in the actual Worksheet. Does anyone have any idea why it does that? Best Regards, John "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:O6hfFkDUGHA.5172@TK2MSFTNGP12.phx.gbl... > You can use CF with a formula of > > =$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) > > for lower. Higher is obvious, but what is the definition of around? > > -- > HTH > > Bob Phillips > > (remove nothere from email address if mailing direct) > > "John" <joja_15@yahoo.com> wrote in message > news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... >> Say I have a worksheet with the following information: >> >> Name Position Salary >> John Producer $10,000 >> Jeremy Producer $98,000 >> Jaime Producer $50,000 >> Darren Artist $67,000 >> Chris Artist $75,000 >> Clint Artist $30,000 >> Adam Artist $57,000 >> >> >> In Cell C2 (salary for John) I want to create a formula that looks for >> all rows that contain the same position as in B2 (John's position which >> is producer) and then calculates the average of all the salaries in >> column C of those rows that have the position producer. Then I want to >> compare that calculated average salary for all rows that have the >> position producer to the value in cell C2. I am using cell C2 as an >> example but I would like to do the same calculation for all salary >> rows. Basically I want to use conditional formatting to show if the >> average salary for that persons position is below, around, or above >> that persons salary. So once I create the conditional formatting formula >> I can copy it to all the cells that have salary. >> >> >> Can the above be done without using VBA or is VBA the only way to do >> it? If VBA is the only way to do it can someone point me in the rough >> direction of how I would go about it? >> >> >> Thank in advance for any help provided. >> >> >> - John >> >> >> >> >> --- >> avast! Antivirus: Outbound message clean. >> Virus Database (VPS): 0612-4, 03/25/2006 >> Tested on: 3/25/2006 12:13:46 PM >> avast! - copyright (c) 1988-2005 ALWIL Software. >> http://www.avast.com >> >> >> > > > > > --- > avast! Antivirus: Inbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 2:57:42 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > > --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0612-4, 03/25/2006 Tested on: 3/25/2006 3:36:28 PM avast! - copyright (c) 1988-2005 ALWIL Software. http://www.avast.com
You need to enter it with ctrl + shift & enter -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "John" <joja_15@yahoo.com> wrote in message news:%23w3arQFUGHA.5108@TK2MSFTNGP11.phx.gbl... > Bob, thank you for the quick response! > > I am getting weird results with that formula. To simplify and debug it I > just took the AVERAGE which was the following: > =AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) > > The above formula returns $55,286 which is the average for all salaries > not just the "Producer" ones. I verified that by doing: =AVERAGE(C2:C8). > The actual value is supposed to be $52,667 which I verified by doing: > =AVERAGE(C2:C4). Now here is the weird thing. If I select the formula in > the text entry bar, click on the fx button at the bottom of the "Function > Arguments" dialogue box it says "Formula result = $52,667". So that > formula returns the correct result in the Function Arguments dialogue box > but it does not return the correct result in the actual Worksheet. Does > anyone have any idea why it does that? > > Best Regards, > John > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message > news:O6hfFkDUGHA.5172@TK2MSFTNGP12.phx.gbl... >> You can use CF with a formula of >> >> =$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) >> >> for lower. Higher is obvious, but what is the definition of around? >> >> -- >> HTH >> >> Bob Phillips >> >> (remove nothere from email address if mailing direct) >> >> "John" <joja_15@yahoo.com> wrote in message >> news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... >>> Say I have a worksheet with the following information: >>> >>> Name Position Salary >>> John Producer $10,000 >>> Jeremy Producer $98,000 >>> Jaime Producer $50,000 >>> Darren Artist $67,000 >>> Chris Artist $75,000 >>> Clint Artist $30,000 >>> Adam Artist $57,000 >>> >>> >>> In Cell C2 (salary for John) I want to create a formula that looks for >>> all rows that contain the same position as in B2 (John's position which >>> is producer) and then calculates the average of all the salaries in >>> column C of those rows that have the position producer. Then I want to >>> compare that calculated average salary for all rows that have the >>> position producer to the value in cell C2. I am using cell C2 as an >>> example but I would like to do the same calculation for all salary >>> rows. Basically I want to use conditional formatting to show if the >>> average salary for that persons position is below, around, or above >>> that persons salary. So once I create the conditional formatting formula >>> I can copy it to all the cells that have salary. >>> >>> >>> Can the above be done without using VBA or is VBA the only way to do >>> it? If VBA is the only way to do it can someone point me in the rough >>> direction of how I would go about it? >>> >>> >>> Thank in advance for any help provided. >>> >>> >>> - John >>> >>> >>> >>> >>> --- >>> avast! Antivirus: Outbound message clean. >>> Virus Database (VPS): 0612-4, 03/25/2006 >>> Tested on: 3/25/2006 12:13:46 PM >>> avast! - copyright (c) 1988-2005 ALWIL Software. >>> http://www.avast.com >>> >>> >>> >> >> >> >> >> --- >> avast! Antivirus: Inbound message clean. >> Virus Database (VPS): 0612-4, 03/25/2006 >> Tested on: 3/25/2006 2:57:42 PM >> avast! - copyright (c) 1988-2005 ALWIL Software. >> http://www.avast.com >> >> >> > > > > > --- > avast! Antivirus: Outbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 3:36:28 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > >
|
0 |
|
Peo
3/25/2006 10:00:46 PM
Tom, your method worked perfectly. I am trying the other method posted by Bob to see if it improves performance as you mentioned. I am getting weird results though where in the worksheet I get one result and in the function arguments dialogue box I get a different result for the formula result line. - John "Tom Ogilvy" <twogilvy@msn.com> wrote in message news:eLT$aFEUGHA.4492@TK2MSFTNGP09.phx.gbl... > If your data covers a lot of rows, I would steer away from the use of > Average as it will slow your calculations down. > > Test it for yourself to see if there is a difference or I am wrong. > > -- > Regards, > Tom Ogilvy > > "Tom Ogilvy" <twogilvy@msn.com> wrote in message > news:u%23uQQ$DUGHA.1672@tk2msftngp13.phx.gbl... >> Select all the cells you want to be highlighted by conditional >> formatting, >> with the active cell being in row 2. >> >> Then do Format=>Conditional Formatting >> >> change Cell Value is to Formula is. >> for the first condition use >> =abs($D2-(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)))>2000 >> click the format button and select the pattern you want >> >> click the add button >> change Cell Value is to Formula is. >> >> =$D2>(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) >> select formatting >> >> click the add button >> change Cell Value is to Formula is. >> =$D2<(Sumif($C:$C,$C2,$D:$D)/Countif($C:$C,$C2)) >> select formatting >> >> -- >> Regards, >> Tom Ogilvy >> >> >> "John" <joja_15@yahoo.com> wrote in message >> news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... >> > Say I have a worksheet with the following information: >> > >> > Name Position Salary >> > John Producer $10,000 >> > Jeremy Producer $98,000 >> > Jaime Producer $50,000 >> > Darren Artist $67,000 >> > Chris Artist $75,000 >> > Clint Artist $30,000 >> > Adam Artist $57,000 >> > >> > >> > In Cell C2 (salary for John) I want to create a formula that looks for >> > all rows that contain the same position as in B2 (John's position which >> > is producer) and then calculates the average of all the salaries in >> > column C of those rows that have the position producer. Then I want to >> > compare that calculated average salary for all rows that have the >> > position producer to the value in cell C2. I am using cell C2 as an >> > example but I would like to do the same calculation for all salary >> > rows. Basically I want to use conditional formatting to show if the >> > average salary for that persons position is below, around, or above >> > that persons salary. So once I create the conditional formatting >> > formula >> > I can copy it to all the cells that have salary. >> > >> > >> > Can the above be done without using VBA or is VBA the only way to do >> > it? If VBA is the only way to do it can someone point me in the rough >> > direction of how I would go about it? >> > >> > >> > Thank in advance for any help provided. >> > >> > >> > - John >> > >> > >> > >> > >> > --- >> > avast! Antivirus: Outbound message clean. >> > Virus Database (VPS): 0612-4, 03/25/2006 >> > Tested on: 3/25/2006 12:13:46 PM >> > avast! - copyright (c) 1988-2005 ALWIL Software. >> > http://www.avast.com >> > >> > >> > >> >> > > > > > --- > avast! Antivirus: Inbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 2:57:42 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > > --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0612-4, 03/25/2006 Tested on: 3/25/2006 4:26:38 PM avast! - copyright (c) 1988-2005 ALWIL Software. http://www.avast.com
Thank you Peo! That worked like a charm! Now I need to do some research to understand what that did. :-) - John "Peo Sjoblom" <peo.sjoblom@^^nwexcelsolutions.com> wrote in message news:%23$zWWeFUGHA.5468@TK2MSFTNGP14.phx.gbl... > You need to enter it with ctrl + shift & enter > > -- > > Regards, > > Peo Sjoblom > > Northwest Excel Solutions > > www.nwexcelsolutions.com > > (remove ^^ from email address) > > Portland, Oregon > > > > > "John" <joja_15@yahoo.com> wrote in message > news:%23w3arQFUGHA.5108@TK2MSFTNGP11.phx.gbl... >> Bob, thank you for the quick response! >> >> I am getting weird results with that formula. To simplify and debug it I >> just took the AVERAGE which was the following: >> =AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) >> >> The above formula returns $55,286 which is the average for all salaries >> not just the "Producer" ones. I verified that by doing: =AVERAGE(C2:C8). >> The actual value is supposed to be $52,667 which I verified by doing: >> =AVERAGE(C2:C4). Now here is the weird thing. If I select the formula in >> the text entry bar, click on the fx button at the bottom of the "Function >> Arguments" dialogue box it says "Formula result = $52,667". So that >> formula returns the correct result in the Function Arguments dialogue box >> but it does not return the correct result in the actual Worksheet. Does >> anyone have any idea why it does that? >> >> Best Regards, >> John >> >> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message >> news:O6hfFkDUGHA.5172@TK2MSFTNGP12.phx.gbl... >>> You can use CF with a formula of >>> >>> =$C2<AVERAGE(IF($B$1:$B$8=$B2,$C$1:$C$8)) >>> >>> for lower. Higher is obvious, but what is the definition of around? >>> >>> -- >>> HTH >>> >>> Bob Phillips >>> >>> (remove nothere from email address if mailing direct) >>> >>> "John" <joja_15@yahoo.com> wrote in message >>> news:OtL5ZfDUGHA.1728@TK2MSFTNGP11.phx.gbl... >>>> Say I have a worksheet with the following information: >>>> >>>> Name Position Salary >>>> John Producer $10,000 >>>> Jeremy Producer $98,000 >>>> Jaime Producer $50,000 >>>> Darren Artist $67,000 >>>> Chris Artist $75,000 >>>> Clint Artist $30,000 >>>> Adam Artist $57,000 >>>> >>>> >>>> In Cell C2 (salary for John) I want to create a formula that looks for >>>> all rows that contain the same position as in B2 (John's position which >>>> is producer) and then calculates the average of all the salaries in >>>> column C of those rows that have the position producer. Then I want to >>>> compare that calculated average salary for all rows that have the >>>> position producer to the value in cell C2. I am using cell C2 as an >>>> example but I would like to do the same calculation for all salary >>>> rows. Basically I want to use conditional formatting to show if the >>>> average salary for that persons position is below, around, or above >>>> that persons salary. So once I create the conditional formatting >>>> formula >>>> I can copy it to all the cells that have salary. >>>> >>>> >>>> Can the above be done without using VBA or is VBA the only way to do >>>> it? If VBA is the only way to do it can someone point me in the rough >>>> direction of how I would go about it? >>>> >>>> >>>> Thank in advance for any help provided. >>>> >>>> >>>> - John >>>> >>>> >>>> >>>> >>>> --- >>>> avast! Antivirus: Outbound message clean. >>>> Virus Database (VPS): 0612-4, 03/25/2006 >>>> Tested on: 3/25/2006 12:13:46 PM >>>> avast! - copyright (c) 1988-2005 ALWIL Software. >>>> http://www.avast.com >>>> >>>> >>>> >>> >>> >>> >>> >>> --- >>> avast! Antivirus: Inbound message clean. >>> Virus Database (VPS): 0612-4, 03/25/2006 >>> Tested on: 3/25/2006 2:57:42 PM >>> avast! - copyright (c) 1988-2005 ALWIL Software. >>> http://www.avast.com >>> >>> >>> >> >> >> >> >> --- >> avast! Antivirus: Outbound message clean. >> Virus Database (VPS): 0612-4, 03/25/2006 >> Tested on: 3/25/2006 3:36:28 PM >> avast! - copyright (c) 1988-2005 ALWIL Software. >> http://www.avast.com >> >> >> > > > > --- > avast! Antivirus: Inbound message clean. > Virus Database (VPS): 0612-4, 03/25/2006 > Tested on: 3/25/2006 4:30:33 PM > avast! - copyright (c) 1988-2005 ALWIL Software. > http://www.avast.com > > > --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0612-4, 03/25/2006 Tested on: 3/25/2006 4:56:25 PM avast! - copyright (c) 1988-2005 ALWIL Software. http://www.avast.com
Similar Artilces:
Analyze Log Drive Space Average daily log generation rate
I have Exchange 2007 SP1 on a Windows 2003 server with netapp LUNS for the server storage space and this box has been running processing mail for just over a year. In that time we have added about 50 mailboxes to Exchange. In checking the log drive available space on a daily basis, I have noticed that the Estimated days for Exchange Server to run before exhausting currently available log drive space has been much less then I would hope for. For example: Available Log disk space [GB] is usually between 31 and 34, Logs [GB] are between 0.021 and 0.16, Average daily log generatio...
help with formula please #2
Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...
Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error
I have a working SUMPRODUCT formula {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need to change the 1st array to C5623. When I do I get a #N/A error. I've tried giving the range a name (empnofm2), and I've verified all the data in the C4:C5623 range is formated the same (general, it is all employee numbers from 2 to 5 digits long. I'm using a "trim all" macro which is working great. I am unable to determine what is causing the #N/A and how to fix it. Basically I copied an entire worksheet within the same workbook and changed the name, wh...
Removing a date by inputting a newer one in another cell
I have office excel 2007. I am a fairly new user, I have a worksheet which maintains and monitors the movement of various units, and is controlled by quantity and dates. What I would like to do is write a formula that will remove the ordered date when I input the new start date, which would be the newer date. So far I have not been able to make this work. I would appreciate any help or advice you can give me. Many Thanks Mike You could use a worksheet_change event that clearscontents of the original cell. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com ...
Auto-populate comboBox with value from Row Source
I am a novice to intermediate Access 2003 user with some VBA experience. I have a form with 3 combo boxes. All three combo boxes have queries which return a single column, as their row source. Upon an update to the first combo box- the row source for the second Combo Box is requeried based on the value from combo box 1. Is it possible to have the (2nd) combo box display (auto-populate) the first value of the resulting (re)query? The drop-downs function correctly based on the queries. I am trying to avoid the user needing to use the drop-down when only one value is returned from ...
help with a formula #5
I have 98 sets of magazines each set has 12 in the set what formula can I use to get a total of issues in all sets have you tried 98*12 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Just Me" <no@isp.com> wrote in message news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl... >I have 98 sets of magazines each set has 12 in the set > > what formula can I use to get a total of issues in all sets > =98*12 or =A1*A2 if the numbers are in A1 and A2 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Just Me" <n...
how to show addresses, not names in to field
i have outlook 2000, Windows XP-Pro. i just want to have the e-mail addresses from my Contacts show in the "TO" field as e-mail addresses, NOT their names. this is help/about/line 2 "Internet Mail Only - Security Update" PLEASE help THANKS! Read the responses to all your other posts. The answer is still no. IMO provides no access to the Display Name field. -- Russ Valentine [MVP-Outlook] "outlook victim" <anonymous@discussions.microsoft.com> wrote in message news:0bbe01c425b5$5fde29c0$a501280a@phx.gbl... > i have outlook 2000, Windows XP-Pro. > i...
Formula #29
Hi, What formula should I use if I want it to look at a specific cell and then work out the total number between two numbers. e.g. if cell H6 had 7-4 entered the number 9 would be returned. Thanks in advance I'm not sure how you got 9, but your life would be much easier if you used two separate cells and then subtracted the smaller from the larger (and then added one???). Boenerge wrote: > > Hi, > What formula should I use if I want it to look at a specific cell and then > work out the total number between two numbers. > e.g. if cell H6 had 7-4 entered the number 9 woul...
Trying to make part of an output in another cell italicized.
Hello all, I have Excel from Office XP and am using it on Windows XP Home Edition I'm working on a program where users enter text in various cells. Fo example: A B 1 Name: ------ 2 Date: ------ Then the text that they enter is displayed in another cell all togethe using the following formula: =$B$4&", "&$B$5&". "&"("&$B$6&"). "&$B$7&". "&$B$8&" "&$B$9&"("&$B$10&"), "&$B$11&"-"&$B$12&"." I'...
Can offset be used in this formula?
Can the offset be used in the below formula for the information in column "K"? In column L have the the following formula's L46 =MAX($I46*1000-$K$46*$J46,0) L47 =MAX($I47*1000-$K$46*$J47,0) .... L99 =MAX($I99*1000-$K$46*$J99,0) What I'd like to do is to copy the fomula into columns M, N, O, ... M47 =MAX($I47*1000-$K$47*$J47,0) M48 =MAX($I48*1000-$K$47*$J48,0) M49 =MAX($I49*1000-$K$47*$J49,0) .... N48 =MAX($I48*1000-$K$48*$J48,0) N49 =MAX($I49*1000-$K$48*$J49,0) .... O50 =MAX($I50*1000-$K$50*$J50,0) O51 =MAX($I51*1000-$K$50*$J51,0) .... How abou...
Forward email arrived to another
My client is having problem in setting the Outlook 2000 SR-1 (9.0.0.3821) to forward the email when it arrives with Rules. The Rule has been setup to 'Apply this rule after message arrives with 'statement' in the subject or body, forward it to 'another one', using Tools -> Rules Wizard. Unfortunately the Rule won't work automatically. When an email arrives with 'statement' in either the subject or body, Outlook 2000 won't do anything. However it will forward if I go into the Rules Wizard and click the Run Now button. My client is running Exchange 2000....
Formula to return tomorrow's date.
I have a report that must be turned in the night before for tomorrow's date. Is ithere a date formula that will return tomorrow's date to a cell in Excel? One way: =TODAY() + 1 In article <668782AC-774A-475F-9821-8A4C3B14A983@microsoft.com>, "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote: > I have a report that must be turned in the night before for tomorrow's date. > Is ithere a date formula that will return tomorrow's date to a cell in Excel? I assume that you want the date to remain static after entry. Easiest way, use 2...
Calender Control
Hi I have just recently found the calender control, I have a date field on my form, how can I set up the calender so that I have a lil button next to the date which when clicked opens up the calender then when you say double-click a date, puts it into the right field and closes the calender again? Thank You -- Message posted via http://www.accessmonster.com Here's a demo: http://www.datastrat.com/Download/XCalendar2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "evilcowstare via AccessMonster.com" <u31650@uwe...
Merging two tables based on a unique data field
I have two text files that I would like to merge into one based on a common data field. One has persons' names and addresses, the other has different information about each person. Both have a unique ID number that can be used to combine the data. What is the best way to bring them together? Using Excel? or Access? What process should I use to get the text data into the program? then to combine the data? I'd appreciate whatever help anyone can give. Thank you, Jeanne Import the data into two separate Access tables. Create a new query using both tables as the data source. Mak...
I want to duplicate rows
my data base contains 1005 rows with various medical information (10 columns) One of the column corresponds to an occurence number and I need to duplicate each row by this number ex row 1, this number is 642 , therefore, I need that row 1 is duplicated 642 times row 2, this number is 55, then 55 duplication and so on the target can be a new sheet is it possible ? it will help thank you Pr Soussi Paris ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ see Mail Me...
To find out number of sundays and saturdays in january month
Hai all, How I can find out the number of saturdays and sundays in January For example in A1 and B1 cell represents Saturdays and Sundays. How I can find out the number of saturdays and sundays in that month and to write in corresponsing A2 and B2 column . Please help with thanks and regards Pol =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=1)) Would tell you the number of Sundays in January of 2008. =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) Would tell you the number of Saturd...
Weekday formula
Can anyone help with the weekday formula. What I want to do is enter the date in say A1 11/20/2003 and have B return the answer of Thursday. Currently I use the weekday formula bu only returns the number of the day. Thanks in advance B ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com There are several ways to do this. One is to enter =A1 and format the cell with a custom number format of dddd. Another way is to use the function =TEXT(A1,"dddd"). -- Cordiall...
Trendline of multiple series #2
Am I able to calculate the trend over multiple sheets to get on inclusive trend using the trend function, if so how do I do i -- geooi ----------------------------------------------------------------------- geooil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1650 View this thread: http://www.excelforum.com/showthread.php?threadid=27890 How many data points? You cannot plot data that stretches over multiple sheets without somehow consolidating it: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html I'm not sure LINEST works with range...
row length
I have Excel 2000. I am trying to make some sample data to read in another program. Each record should consist of 9 sets of 14 fixed characters; ie. 11111111111111,22222222222222,33333333333333 etc thru the ninth set. Each set is in a separate column. When I export this file to my desktop I seem to be only getting 90 characters across. No more. What is wrong? How are you exporting the data? Maybe file|saveas and use .csv (comma separated values). rob p wrote: > > I have Excel 2000. I am trying to make some sample data to read in another > program. > > Each record shou...
Formula to return a formula
At least I think that's what I'm looking for. here's my situation: I've got a sheet where there is a variable (X) that changes depending on what row it is on. So I have a column (column c) that lists these variables (X is dependant on things from a different sheet). X is a muliplier that is utilized differently depending on the value of N. N is a Picklist selection and can change periodically. Or even be duplicated on more than one row. I have a vlookup that checks the value of N (column a) and needs to return a formula that has X applied if needed on the approp...
Migrating from 5.5 to 2003 #2
I have just about completed the migration from Exchange 5.5 to 2003. We have moved all the mailboxes and changed the DNS entries to the new server IP. All mail is now being sent to the new server but if I stop the Exchange service on our 5.5 server no e-mail is being sent out. I figured this part out when I noticed the queue on the new server pointing to the old server with the protocol x400 had a lot of unsent messages. Obviously the mail is being routed from the new server thru the old server but I cannot figure out where to change this so it send from the new server. Any ideas? Hi Motley...
add field to form from different table
I have a form - record source is t0103Issue.(see below for fields) I would like to add a text box with a field (LevelorAdj) from a different table(see below for LevelorAdj) Textbox is named LJ table t013IssueDesc (add LevelorAdj to form) IssueDescID IssueDesc LevelorAdj table t0103Issue (for form) IssueID RiskID IssueDescID How is this done? -- deb Deb, in the form's current event, add: LJ=dlookup("LevelorAdj","t013IssueDesc","IssueDescID = " & [IssueDescID]) UpRider "deb" <deb@discussions.microsoft.com> wrote in message news:A710...
Copy macro to other row
Hi all, I've created two macron that executes calculations on a certain row. Can I copy these macros so that they can execute the same calculations but on a another row i.e. only on one row at the time leaving the others unaffected. Regards Dan As always, post your coding efforts for comments -- Don Guillett SalesAid Software dguillett1@austin.rr.com "togge" <dantorgny@spray.se> wrote in message news:1158919664.025298.246070@b28g2000cwb.googlegroups.com... > Hi all, > > I've created two macron that executes calculations on a certain row. > Can I cop...
Count formula within a named range.
Hi, How do I change the following formula =SUMIF($F$39:$F$79,"PW Shopfitters",$D$39:$D$79)/COUNTIF($F$39:$F$79,"PW Shopfitters") to count within a named range (PW Shopfitters). Any help would be great. Cheers, Phil Hi A range name cannot have spaces, maybe that's your problem. Name your range PW_Shopfitters. Then replace your cell references with PW_Shopfitters, eg =Sumif(PW_Shopfitters,"PW Shopfitters",............ -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "PW11111" wrote: > > Hi, > &g...
I have Exchange 2007 SP1 on a Windows 2003 server with netapp LUNS for the server storage space and this box has been running processing mail for just over a year. In that time we have added about 50 mailboxes to Exchange. In checking the log drive available space on a daily basis, I have noticed that the Estimated days for Exchange Server to run before exhausting currently available log drive space has been much less then I would hope for. For example: Available Log disk space [GB] is usually between 31 and 34, Logs [GB] are between 0.021 and 0.16, Average daily log generatio...
help with formula please #2
Can anyone please help with this formula. If Sheet1 Column A = nothing(blank) And Column B = \\\\\\CC\\\\\\ Then Sheet 2 B7 = SumTotal. As a regular formula would be nice or VB code Hi Richard you want nothing at all in the whole of column A in sheet 1? do you want every cell in column B of sheet 1 to have \\\\\\CC\\\\\\ or should this (can this) only appear in one cell? and what range are you summing on sheet 2 in cell B7? Cheers JulieD "Richard" <anonymous@discussions.microsoft.com> wrote in message news:82ce01c477e1$77f0e740$a301280a@phx.gbl... > Can anyone please ...
Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...
Changing a range of an array in a SUMPRODUCT formula gives a #N/A error
I have a working SUMPRODUCT formula {=SUMPRODUCT(($C$4:$C$4341=z8)*(audittypefm1="Standard"))} that I need to change the 1st array to C5623. When I do I get a #N/A error. I've tried giving the range a name (empnofm2), and I've verified all the data in the C4:C5623 range is formated the same (general, it is all employee numbers from 2 to 5 digits long. I'm using a "trim all" macro which is working great. I am unable to determine what is causing the #N/A and how to fix it. Basically I copied an entire worksheet within the same workbook and changed the name, wh...
Removing a date by inputting a newer one in another cell
I have office excel 2007. I am a fairly new user, I have a worksheet which maintains and monitors the movement of various units, and is controlled by quantity and dates. What I would like to do is write a formula that will remove the ordered date when I input the new start date, which would be the newer date. So far I have not been able to make this work. I would appreciate any help or advice you can give me. Many Thanks Mike You could use a worksheet_change event that clearscontents of the original cell. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com ...
Auto-populate comboBox with value from Row Source
I am a novice to intermediate Access 2003 user with some VBA experience. I have a form with 3 combo boxes. All three combo boxes have queries which return a single column, as their row source. Upon an update to the first combo box- the row source for the second Combo Box is requeried based on the value from combo box 1. Is it possible to have the (2nd) combo box display (auto-populate) the first value of the resulting (re)query? The drop-downs function correctly based on the queries. I am trying to avoid the user needing to use the drop-down when only one value is returned from ...
help with a formula #5
I have 98 sets of magazines each set has 12 in the set what formula can I use to get a total of issues in all sets have you tried 98*12 -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Just Me" <no@isp.com> wrote in message news:eZ66ZEGHGHA.1192@TK2MSFTNGP11.phx.gbl... >I have 98 sets of magazines each set has 12 in the set > > what formula can I use to get a total of issues in all sets > =98*12 or =A1*A2 if the numbers are in A1 and A2 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Just Me" <n...
how to show addresses, not names in to field
i have outlook 2000, Windows XP-Pro. i just want to have the e-mail addresses from my Contacts show in the "TO" field as e-mail addresses, NOT their names. this is help/about/line 2 "Internet Mail Only - Security Update" PLEASE help THANKS! Read the responses to all your other posts. The answer is still no. IMO provides no access to the Display Name field. -- Russ Valentine [MVP-Outlook] "outlook victim" <anonymous@discussions.microsoft.com> wrote in message news:0bbe01c425b5$5fde29c0$a501280a@phx.gbl... > i have outlook 2000, Windows XP-Pro. > i...
Formula #29
Hi, What formula should I use if I want it to look at a specific cell and then work out the total number between two numbers. e.g. if cell H6 had 7-4 entered the number 9 would be returned. Thanks in advance I'm not sure how you got 9, but your life would be much easier if you used two separate cells and then subtracted the smaller from the larger (and then added one???). Boenerge wrote: > > Hi, > What formula should I use if I want it to look at a specific cell and then > work out the total number between two numbers. > e.g. if cell H6 had 7-4 entered the number 9 woul...
Trying to make part of an output in another cell italicized.
Hello all, I have Excel from Office XP and am using it on Windows XP Home Edition I'm working on a program where users enter text in various cells. Fo example: A B 1 Name: ------ 2 Date: ------ Then the text that they enter is displayed in another cell all togethe using the following formula: =$B$4&", "&$B$5&". "&"("&$B$6&"). "&$B$7&". "&$B$8&" "&$B$9&"("&$B$10&"), "&$B$11&"-"&$B$12&"." I'...
Can offset be used in this formula?
Can the offset be used in the below formula for the information in column "K"? In column L have the the following formula's L46 =MAX($I46*1000-$K$46*$J46,0) L47 =MAX($I47*1000-$K$46*$J47,0) .... L99 =MAX($I99*1000-$K$46*$J99,0) What I'd like to do is to copy the fomula into columns M, N, O, ... M47 =MAX($I47*1000-$K$47*$J47,0) M48 =MAX($I48*1000-$K$47*$J48,0) M49 =MAX($I49*1000-$K$47*$J49,0) .... N48 =MAX($I48*1000-$K$48*$J48,0) N49 =MAX($I49*1000-$K$48*$J49,0) .... O50 =MAX($I50*1000-$K$50*$J50,0) O51 =MAX($I51*1000-$K$50*$J51,0) .... How abou...
Forward email arrived to another
My client is having problem in setting the Outlook 2000 SR-1 (9.0.0.3821) to forward the email when it arrives with Rules. The Rule has been setup to 'Apply this rule after message arrives with 'statement' in the subject or body, forward it to 'another one', using Tools -> Rules Wizard. Unfortunately the Rule won't work automatically. When an email arrives with 'statement' in either the subject or body, Outlook 2000 won't do anything. However it will forward if I go into the Rules Wizard and click the Run Now button. My client is running Exchange 2000....
Formula to return tomorrow's date.
I have a report that must be turned in the night before for tomorrow's date. Is ithere a date formula that will return tomorrow's date to a cell in Excel? One way: =TODAY() + 1 In article <668782AC-774A-475F-9821-8A4C3B14A983@microsoft.com>, "Shadyhosta" <Shadyhosta@discussions.microsoft.com> wrote: > I have a report that must be turned in the night before for tomorrow's date. > Is ithere a date formula that will return tomorrow's date to a cell in Excel? I assume that you want the date to remain static after entry. Easiest way, use 2...
Calender Control
Hi I have just recently found the calender control, I have a date field on my form, how can I set up the calender so that I have a lil button next to the date which when clicked opens up the calender then when you say double-click a date, puts it into the right field and closes the calender again? Thank You -- Message posted via http://www.accessmonster.com Here's a demo: http://www.datastrat.com/Download/XCalendar2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "evilcowstare via AccessMonster.com" <u31650@uwe...
Merging two tables based on a unique data field
I have two text files that I would like to merge into one based on a common data field. One has persons' names and addresses, the other has different information about each person. Both have a unique ID number that can be used to combine the data. What is the best way to bring them together? Using Excel? or Access? What process should I use to get the text data into the program? then to combine the data? I'd appreciate whatever help anyone can give. Thank you, Jeanne Import the data into two separate Access tables. Create a new query using both tables as the data source. Mak...
I want to duplicate rows
my data base contains 1005 rows with various medical information (10 columns) One of the column corresponds to an occurence number and I need to duplicate each row by this number ex row 1, this number is 642 , therefore, I need that row 1 is duplicated 642 times row 2, this number is 55, then 55 duplication and so on the target can be a new sheet is it possible ? it will help thank you Pr Soussi Paris ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ see Mail Me...
To find out number of sundays and saturdays in january month
Hai all, How I can find out the number of saturdays and sundays in January For example in A1 and B1 cell represents Saturdays and Sundays. How I can find out the number of saturdays and sundays in that month and to write in corresponsing A2 and B2 column . Please help with thanks and regards Pol =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=1)) Would tell you the number of Sundays in January of 2008. =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2008,1,1)&":"&DATE(2008,1,31))),1)=7)) Would tell you the number of Saturd...
Weekday formula
Can anyone help with the weekday formula. What I want to do is enter the date in say A1 11/20/2003 and have B return the answer of Thursday. Currently I use the weekday formula bu only returns the number of the day. Thanks in advance B ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com There are several ways to do this. One is to enter =A1 and format the cell with a custom number format of dddd. Another way is to use the function =TEXT(A1,"dddd"). -- Cordiall...
Trendline of multiple series #2
Am I able to calculate the trend over multiple sheets to get on inclusive trend using the trend function, if so how do I do i -- geooi ----------------------------------------------------------------------- geooil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1650 View this thread: http://www.excelforum.com/showthread.php?threadid=27890 How many data points? You cannot plot data that stretches over multiple sheets without somehow consolidating it: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html I'm not sure LINEST works with range...
row length
I have Excel 2000. I am trying to make some sample data to read in another program. Each record should consist of 9 sets of 14 fixed characters; ie. 11111111111111,22222222222222,33333333333333 etc thru the ninth set. Each set is in a separate column. When I export this file to my desktop I seem to be only getting 90 characters across. No more. What is wrong? How are you exporting the data? Maybe file|saveas and use .csv (comma separated values). rob p wrote: > > I have Excel 2000. I am trying to make some sample data to read in another > program. > > Each record shou...
Formula to return a formula
At least I think that's what I'm looking for. here's my situation: I've got a sheet where there is a variable (X) that changes depending on what row it is on. So I have a column (column c) that lists these variables (X is dependant on things from a different sheet). X is a muliplier that is utilized differently depending on the value of N. N is a Picklist selection and can change periodically. Or even be duplicated on more than one row. I have a vlookup that checks the value of N (column a) and needs to return a formula that has X applied if needed on the approp...
Migrating from 5.5 to 2003 #2
I have just about completed the migration from Exchange 5.5 to 2003. We have moved all the mailboxes and changed the DNS entries to the new server IP. All mail is now being sent to the new server but if I stop the Exchange service on our 5.5 server no e-mail is being sent out. I figured this part out when I noticed the queue on the new server pointing to the old server with the protocol x400 had a lot of unsent messages. Obviously the mail is being routed from the new server thru the old server but I cannot figure out where to change this so it send from the new server. Any ideas? Hi Motley...
add field to form from different table
I have a form - record source is t0103Issue.(see below for fields) I would like to add a text box with a field (LevelorAdj) from a different table(see below for LevelorAdj) Textbox is named LJ table t013IssueDesc (add LevelorAdj to form) IssueDescID IssueDesc LevelorAdj table t0103Issue (for form) IssueID RiskID IssueDescID How is this done? -- deb Deb, in the form's current event, add: LJ=dlookup("LevelorAdj","t013IssueDesc","IssueDescID = " & [IssueDescID]) UpRider "deb" <deb@discussions.microsoft.com> wrote in message news:A710...
Copy macro to other row
Hi all, I've created two macron that executes calculations on a certain row. Can I copy these macros so that they can execute the same calculations but on a another row i.e. only on one row at the time leaving the others unaffected. Regards Dan As always, post your coding efforts for comments -- Don Guillett SalesAid Software dguillett1@austin.rr.com "togge" <dantorgny@spray.se> wrote in message news:1158919664.025298.246070@b28g2000cwb.googlegroups.com... > Hi all, > > I've created two macron that executes calculations on a certain row. > Can I cop...
Count formula within a named range.
Hi, How do I change the following formula =SUMIF($F$39:$F$79,"PW Shopfitters",$D$39:$D$79)/COUNTIF($F$39:$F$79,"PW Shopfitters") to count within a named range (PW Shopfitters). Any help would be great. Cheers, Phil Hi A range name cannot have spaces, maybe that's your problem. Name your range PW_Shopfitters. Then replace your cell references with PW_Shopfitters, eg =Sumif(PW_Shopfitters,"PW Shopfitters",............ -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gauteng, South Africa "PW11111" wrote: > > Hi, > &g...