Multiple Data Ranges in this Countif

Can any guru help me to edit this formula to multiple data ranges as my 
quarter TOTAL column are not side by side.

"Stefi" wrote:

> Try this:
> =IF(COUNTIF(E2:H2,">"&0)>=3,"FTF","PT")
> 
> Anyway, yor second criterium is ambiguous:
> 
> > Low Frequency of sales will be PT type. Low Frequency of sales is defined as 
> > NOT having Revenue $ in at least 3 out of the last 4 quarters. That is, only 
> > 1 or 2 quarters having revenue$ from Q408 to Q309 (Q408, Q109, Q209 and Q309).
> 
> at least 3 out of the last 4 allows only one quarter having revenue!
> 
> Regards,
> Stefi
> 
> 
> „Tan” ezt írta:
> 
> > Hi all, can any excel guru advice me a workaround to resolve these?
> > 
> > I needs to assign a coverage type for each customer, that is FTF (short for 
> > face2face) or PT (short for Proactive Tele) into Coverage column in below 
> > revenue table.
> > 
> > Only High Frequency of sales will be FTF type. High Frequency of sales is 
> > defined as having Revenue $ in at least 3 out of the last 4 quarters. That 
> > is, any of the 3 quarters having revenue$ from Q408 to Q309 (Q408, Q109, Q209 
> > and Q309).
> > 
> > Low Frequency of sales will be PT type. Low Frequency of sales is defined as 
> > NOT having Revenue $ in at least 3 out of the last 4 quarters. That is, only 
> > 1 or 2 quarters having revenue$ from Q408 to Q309 (Q408, Q109, Q209 and Q309).
> > 
> > Below is an extracted sample of my revenue file with each quarter TOTAL 
> > revenue$ (quarter TOTAL column are not side by side) and customer name:
> > 
> > Cust Nm  Coverage   Q108  Q208  Q308  Q408  Q109  Q209  Q309
> > ABC Ltd                    1000   2000  3000   4000  2000   2000  7000
> > XYZ Ltd                    2000   4000  6000   8000   0       0        5000
> > JKL  Ltd                    1000   1000  3000   5000  7000   4000   0
> > 
> > Thanks a bunch
0
Utf
11/24/2009 3:46:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
813 Views

Similar Articles

[PageSpeed] 26

Excel 2007
A PivotTable approach to
"Last n Quarters"
http://c0444202.cdn.cloudfiles.rackspacecloud.com/11_24_09.xlsx
0
Herbert
11/24/2009 7:49:16 PM
Reply:

Similar Artilces:

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I print to PDF, multiple files are created. How can I avoid this? ...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Multiple DHCP servers
I have 2 DHCP servers setup in my domain using the 80/20 rule that Microsoft recommends. I have problems with the 2nd DHCP server however, because it continues to run out of available address leases as it would appear clients default to pulling their addresses from the DHCP server with fewer addresses. Is there a way to default clients to pull their address from a specific DHCP server first and when unavailable, from the second DHCP server? Hi JT Welcome to the Microsoft Macintosh Office newsgroup. Personally, I would like my computers, refrigerator and heating system to each have th...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

Attachments go multiple times
Outlook 2002 SP3 Win XP DSL connection When sending attachments the status pane shows "task 1 of 1" and there is a progress bar. When the progress bar reaches 100%, the status then goes to "task 2 of 2" with the progress bar starting again. This generally repeats itself several times before the message finally moves to the Sent Items folder. In the meantime, the recipient has received several copies of the message. Anyone know what is happening here, and how to stop it? Cheers, ...

Importing multiple identities from Express to Outlook
Help! I have been able to import everything from my main identity from Outlook Express to Outlook, but cannot figure out how to import from my other identity. Please Help! Thanks! bay157 <bay157@discussions.microsoft.com> wrote: > Help! I have been able to import everything from my main identity > from Outlook Express to Outlook, but cannot figure out how to import > from my other identity. Please Help! Thanks! Make the other identity the main identity and then you'll be able to import that. Remember to export mail mesasges from Outlook Express. Do not import m...

Switchboard items on multiple switchboard
I have added 3 switchboard items to a database, but it is showing up on all of the other switchboards. How do I fix this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200710/1 ...

calendar showing multiple months
I'm looking for a calendar that will let a user pick MULTIPLE days from a large calendar (6 months or maybe a year). Stephen Lebans version looks good except I don't think it supports multiple day selecting. The days selected might be the first wednesday of each month for 5 months for example. I would use this to then create records in the database for those days selected. Can anyone point me to a NON ActiveX version. I already know of some good activeX versions (but hoping to avoid using ActiveX for this project, since I only need date selection). Mark ...

How do I save multiple pages in a single .tif file?
I have 30 pages in a visio file that I want to share using the tif format. How can I save all 30 pages in a singe .tif document. ...