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
 0
PW11111 (15)
7/19/2005 7:56:03 AM
excel.misc 78881 articles. 5 followers.

2 Replies
1294 Views

Similar Articles

[PageSpeed] 49

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,
>
> 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
 0
7/19/2005 8:15:03 AM
Thats great.

Thank-You.

"Kassie" wrote:

> 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,
> >
> > 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
 0
PW11111 (15)
7/19/2005 8:29:01 AM

Similar Artilces:

Excel function names in different languages
Hi all, One of the most annoying "features" in excel has been that excel only recognizes the function names in the language the excel is for. I myself have Finnish (language from Finland) excel at home and English excel at work. Now you can believe the mess I'm in when I have used excel at work whole day using english functions and start to use excel at home. That's just not very convinient. Is there any way to enable i.e. Finnish excel to understand standard english functions? Is there even a reference list out there where I could check what any given english function nam...

Naming columns in Excel
I am trying to set up a database in Excel. I need to change the columns (A, B, C,...etc) to name, date, ssn, etc. I am having trouble and dont know how to do it. PLEASE HELP! I want to be able to have each column name to reflect the information that will be put in that respective column. Thank you for your help Hi you can't change the column headers. The best you could do is use the first row as heading row "Peggy" wrote: > I am trying to set up a database in Excel. I need to change the columns (A, > B, C,...etc) to name, date, ssn, etc. I am having troubl...

Formula Excel
Wondering if someone would be able to help me with this: A B C Year Year Service 01-Sep-89 30-Jun-10 = 21 Years Teachers From September 1, 1989 to June 30, 1990 = 1 Year of Service. I know it only 10 months, but that is their year. Is there a formula that could caculate the number of years from September 1, 1989 to June 30, 2010. -- Newfie The general formula for years between dates is =Year(latest date) - Year(earlier date) and 2010-1989 = 21 years. So that might work for most cases? A complication arises if they begin, but do...

im a teenager trying to do a project for school on excel. i need to re-do my grade history sheet on excel. this is fine, i've done it all, and i'm fine on that part. where i'm lost at is the points part. the thing is for every class i have a grade. for every grade there are points (A=4, B=3, C=2, D=1, F=0). that and also, if one of my classes is honors gifted, a bit higher in level, you get an extra point (A=5, B=4, C=3, D=2, F=1). and if you're in an AP class, advanced placement, it's 2 more points (A=6, etc.). what i need to know is a formula so that whatever grade...

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Help with formula for IF ANY
Is there an ANY formula for an excel Cell?? i want to set the conditional formatting for a range of cells in a excel 2007 column to the text "OFF" currently i have the formatting for each cell in the range to highlight if the value contains "OFF" i would like to have the entire range of cells highlighted if ANY of the cells in the range contain "OFF" Am i nuts or is this possible?? Thanks for your help Select your range (say C7:e9) Then use formula is: =countif(\$c\$7:\$e\$9,"off")>0 And apply the format you like. Barry A&P wrote: > >...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

Formula in MSP
Hi, I am using MSP 2003. I want to know about all the customizing formulas used in MS project with examples. It is there any website, how the formula we can use in ms project?. Thanks In the Project Help screen, type in "formula" and you should get an option for all formulas in custom fields. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > I am using MSP 2003. I want to know about all the customizing > formulas > used in MS project with examples. It is there any website, how the > formula we > can use in ms project?. ...

Pasting a Formula into a new column or worksheet, but so that it doesn't change
Hello - When I copy and paste a formula into a new column, Excel assumes that I wanted to use references to different columns. That is, each time that I want to copy and paste a formula into a new column, or sheet, I have to either first make it an absolute reference---or I have to paste it into Word, and then paste it into Excel to keep it as the same formula. Does anyone know if there is an easier way to paste a formula so that the reference doesn't change, and the formula thus stays the same? Thanks!! One way is to copy it *from the formula bar*! Click in the cell containing the fo...

Formulas don't work in certain cells #3
One formula is: =SUM(C10:C15) another: =SUM(O16/6363) another: =SUM(C19:C36) these same formulas work in the cells right next to these wit different cell references. However, no matter which cell I put th above formulas in, the result is the same - either blank with a dash i the cell or zero. I checked all the cells that the formulas refer t and none has any errors. -Jorda -- kalik24 ----------------------------------------------------------------------- kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View...

Linking a defined range to a picture in PowerPoint
Hi, I know this is a long shot, but I'm hoping SOMEBODY has the answer.... I need to create a power point presentation that links to an excel workbook. The reason I need to link it is because the numbers will change monthly, and the presentation is distributed monthly. So, I need to create a presentation that will: 1) have excel worksheets in it that link to defined ranges in a workbook. 2) HOPEFULLY have the Pow.Pt. worksheet appear as a bitmap (if not thats ok) 3) allow the Pow.Pt. SS (or the picture) to link to the SS so that the image will change every month. I know it'...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

OnClick event name
*************************************************** public class UserControl1 : System.Windows.Forms.UserControl { // ... public event EventHandler OnClick; // ... public void SomeFuncWhichFiresClickEvent() { // ... // fire event if (OnClick != null) OnClick(this, new EventArgs()); } // ... }public class UserControl1 : System.Windows.Forms.UserControl { // ... public event EventHandler OnClick; // ... public void SomeFuncWhichFiresClickEvent() { // ... // fire event if (OnClick != null) OnClick(t...

not calculating formulas
in some excel sheets, after a while it stops calculating formulas. if i copy the hole sheet and paste it into another, everything works fine. Any chance that on the: tools|options|Calculation tab| calculation was set to manual? doros wrote: > > in some excel sheets, after a while it stops calculating formulas. if i copy > the hole sheet and paste it into another, everything works fine. -- Dave Peterson No. I have allready checked that. "Dave Peterson" wrote: > Any chance that on the: > tools|options|Calculation tab| > calculation was set to manual? > &...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

Is there any way to use longer strings in formulas?
In Excel 2002, the longest string I can use in a formula is (to my mind) ludicrously short. I have a workbook that requires HUNDREDS of English sentences like: ="Just to let y'all know, thanks to all the swell work by our sales staff in 2007, and despite our having to fire half of them for insubordination, and despite our chairman's having run off to a region in Asia which we believe is officially called the Glorious Former Soviet Autonomous People's Provisional Revolutionary Socialist Republic of Trans-Caucasian Equitorial Guinea, our company's annual return on investme...

locking formulas?
Is there a way to lock formulas??? For example, I am producing this spreadsheet to distribute to several departments -- each department will enter their own information, but if they enter wrong info and delete it, the formulas are deleted as well...how do i lock formulas, but allow users to enter info?? Hi first select the cells for which you want to allow entries. Then goto 'Format - Cells - Protection' and uncheck 'Locked'. Afterwards protect the sheet -- Regards Frank Kabel Frankfurt, Germany internet team wrote: > Is there a way to lock formulas??? For example,...

Creating a conditional formula to increment #s in a test script
Greetings, I have an Excel question. I am creating a test script template for our team and I an in need of some formula help. Here is what I wish to do: Let's say I have a column A in my test script. I wish to label this column with my test script # TC1 TC2 TC3...(and so on) However, the problem is I have section breaks in my test script that divides the document into sections: eg.. <LOGIN SECTION> TC1 TC2 TC3 <LOGOUT SECTION> TC4 TC5 TC6 Now the problem is that if I want to add a new test case to the "Login section" above, I need ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! Look for =workday() in Excel's help. (Also part o...

"PasteSpecial method of Range class failed" occurs in one instance of Excel but not another
Hi All, I have this particular piece of code in an application created on Excel2000: .. . . . . ..range("B" & finger).PasteSpecial xlPasteValues ..range("B" & finger).PasteSpecial xlPasteFormats .. . . . . I open the application in Excel2000 on one PC and I get the "PasteSpecial method of Range class failed" If I open the same application in Excel2000 on a different PC I do not get the error and the application works as expected. Can anyone tell me why this happens? Thanks in advance, Andrew On 7 Jul 2004 19:00:59 -0700, nocalves@hotmail.com (Andre...

Clearing names in AutoComplete
I have the AutoComplete feature turned on in Outlook 2002. However one of the names/address that it resolves to is incorrect. Where are these names stored and is there a way to edit them. (i.e. I type in John Doe and it enters the emaill address jon.doe@abc.com, but I need to change the email address to john.doe@abc.com) These are not entries that are in contacts they are just names of people that I have sent emails to in the past. Any thoughts? Remove individual addresses from the autocompletion cache by highlighting the entry when presented in the suggested names list (use your ...

Formula wanted
Hi. I have two columns in Excel. In first column A1:A100 i have some integers and empty cells and in second B1:B100 I have some stings and also some empty cells. I would like to apply a formula in C1:C100 somehow in a way: a) to include all stings Bi that the corresponding Ai are numbers b) with non blank cells in C1:C100 (That means all the strings in C column will be in successive order. For example C1=ABC, C2=GEORGE, C3=CCD e.g.) Any idea? Thank you. Try this array formula =IF(ISERROR(SMALL(IF(\$A\$1:\$A\$20<>"",ROW(\$A\$1:\$A\$20)),ROW(A1))),"", ...

Date range for a report
Hi I am trying to run a query and can't seem to nail down the right code. We use the access database to track files, incoming/outgoing correspondence dates etc. I am trying to run a query that show me files with dates in a follow up field of -60 days to +7 days, so essentially any follow ups missed in the last 2 months and up coming in the next week. This report is run on a weekly basis. Appreciate any help Thanks Assuming that the follow up field is actually a date/time data type, try this in the criteria: Between Date() - 60 and Date() + 7 -- Jerry Whittle, ...