I have the following formula. =SUMIFS(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) to =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

i am new to excell and need help with a problem...... the problem is iam looking for a amount < = 3000.00 and iam to display in bold and in light green i have never had to deal with that i got as far as =IF<=3000 then iam lost or it that even right?? I'm far from a whiz at Excel but I'd use conditional formatting. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "kimmy" <kimmy@discussions.microsoft.com> wrote in message news:ADC77884-FC87-47A3-90F2-D2AAF957F5D1@microsoft.com... >i a...

I have entered the formula =IF(OR(D5="K",D5<=4,M32<=95),"INELIGIBLE"," ") I am trying to get a result that will display INELIGIBLE when the value in D5 is K,1,2,3,4 OR the value in M32 is less than 96. with this formula I now get INELIGIBLE when D5 is blank. I would like cell to be blank unless one of the specific conditions is met. If either D5 or M32 is blank, I would like this cell to also be blank M32 contains a formula that returns a blank cell unless data is entered elsewhere in the workbook Thanks for all the help -- dbconn...

I have been tasked with tracking if people fill in their time sheets in on time. I was able to find the following code to track and copy changes to rows within a spreadsheet, but I need to modify it to be able to do columns instead any help would be appreciated. -------------- Dim myRows() As Long Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count > 1 Then Exit Sub On Error GoTo NotDimmed test = UBound(myRows) GoTo Dimmed NotDimmed: ReDim myRows(1 To 1) Dimmed: For i = 1 To UBound(myRows) If myRows(...

How does one manage such a query in the simplistic "Help" offered by these people? Where can I find the answer to my question? "Icon" in the search and help facility treat it only as icon the symbol in Windows, not as a specific header in my Inbox in Outlook 2003, the Email program of Microsoft Office. How does one manage a post in which the user failed even to post a question? You force us to guess what it is. That wastes our time and yours. My guess as to what your question is would have this answer: http://www.howto-outlook.com/howto/icons.htm -- Russ Valentine [...

I need CRM Customization training. Does anyone know where I can receive CRM classroom training in California? Customersource only lists training sessions in Canada. Thanks, Jonathan N. I can get you in touch with contractor-based training. Please send an email to my profile's address. Dave "jonn" wrote: > I need CRM Customization training. > Does anyone know where I can receive CRM classroom training in California? > Customersource only lists training sessions in Canada. > > Thanks, > Jonathan N. ...

I have the following formula to calculate some numeric data: =SUMIF(F6:F27,F5,S6:S27) (this is in cell S5) the qualifying code is in column F and the numeric data to sum is in column S Problem. When I insert a row just below row 5, the formula in S5 changes to: =SUMIF(F7:F27,F5,S7:S27) I will be adding data to the new row6 and I need it to become a part of the calculation. I need the formula to remain F6: and S6: How can I do that? Glen One way =SUMIF(INDIRECT("F6:F27"),F5,INDIRECT("S6:S27")) -- Regards, Peo Sjoblom "Glen Mettler" <glen.e.m...

Hi I have a worksheet that calculates targets for 29 people based on thier percentage share of customers within an area using s sumif formula. My problem is that by allocating % share of a monthly target excel calculates to decimal points and i then have to overtype results so they add up particularly where monthly targets are low. Is there a way that I can get excel to round highest numbers in a range up to interger and lowest results are rounded down? Hope someone can help. To round to the nearest integer: =ROUND(your_formula_here,0) -- Biff Microsoft Excel MVP ...

Hi there, i'm attempting to implement a cross-sheet formula to count the occurances of a single phrase or grouping in one sheet, producing a simple total figure in a cell in another. To put it into real terms, in one sheet there is a collumn with a series of initials indicating an occurence related to their performance - which I am looking to produce a small scorecard in a separate sheet for each user. Eh Sheet 1 - B7:B47 have phrases such as GD (KS), AG (KS) etc etc etc - I need a formula which counts the occurances of a specified such phrase, say GD (KS) and totals the figure in ...

What is the correct syntax to write a statement that, in english, does this If cell L4 = 0 Then delete cells K4 through M4 and shift the cells up Better yet can something be written that checks all L cells for the 0 value and if it finds 0 then deletes the adjacent K through M cells and shifts all cells up TIA ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

A bit stuck hope someone can help. Am trying to copy a vertical range from a series of sheets onto on reference sheet horizontally. Any ideas? -- Berni ----------------------------------------------------------------------- Bernie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1457 View this thread: http://www.excelforum.com/showthread.php?threadid=26197 you can Copy, Paste Special - Transpose -- icestationzbr ----------------------------------------------------------------------- icestationzbra's Profile: http://www.excelforum.com/member.php?action=...

I use several mail accounts, all of which are accessed via POP3. I have set up rules using the Outlook 2002 wizard that move certain incoming messages from the inbox to other folders, based on various criteria. Under Outlook 2000, those folders would get opened up in the tree list so that the bold folder names (for folders with the filtered, unread messages) could be seen. With Outlook 2002, even that small indication of filtered, unread mail is gone. I've found messages over a week old because I was unaware of this (mis)behavior. I have set up a saved search that locates all unread messa...

Hi everybody, I am looking for a simple way to resolve the following problem - don't worry if it sounds too architectural, you don't have to be an architect to solve this! I have a table of assumptions organised like this: Landuse Unit size Type 1 Type 2 Type 3............n Apt - 2bed 100m2 10% 50% 50% Apt - 1bed 60m2 20% 50% Shop 40m2 70% 50% ..... N I'd like to sum the total number of units based on the Type in another column (on another page) by di...

I have a table full of items, below is one example from the table. I am trying to do a query that will display the longest dimension (as L1) for each item. Item field is text, all others are numbers. Here is my data: Item = Y10227 ItemLength = 8.375 ItemWidth = 9.25 ItemHeight = 5.125 Here is the field in my query to determine the largest of the dimensions: L1: IIf([ItemLength]>[ItemHeight] And [ItemWidth],[ItemLength],IIf([ItemHeight]>[ItemLength] And [ItemWidth],[ItemHeight],IIf([Itemwidth]>[itemheight] And [itemlength],[itemwidth]))) The result I am gettin...

This spreadsheet has 5 columns. In Column B, "District," you input the number of a school district. There are 20 districts, so these will between 1 and 20, inclusive. Each district will appear many times in the column (there are over 1,500 rows). Now: Dave is assigned to districts 1,3,4,6,9,17 and 18. Bill is assigned to districts 2,5,11,12,14,15 and 20 Mary is assigned to districts 7,8,10,13,16 and 19. (I'm guessing at these assignments - the actual assignments may be slightly different) What I need to do is configure this spreadsheet so that when the district number is ent...

I basically have a collum of dates and I want to have a descriptive cell with the range of dates. The dates are all entered from another sheet by a user and this page is a summary page the formula looks something like: =A25&"-"&IF(AND(A26=0,A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A34=0,A3 5=0,A36=0,A37=0),A25,IF(AND(A27=0,A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A26,IF(AND(A28=0,A29=0,A30=0,A31=0,A32=0,A33=0,A3 4=0,A35=0,A36=0,A37=0),A27 with A25 being the first date and the sequence of IF statements determining what the end date should be. This works...

Hi ALL, I have a query which has the following fields: ROOMS RESNAME ARRIVAL DEPARTURE I have added another field in my query named ROOMS IN as an expression where I want it to return TODAY by looking at another table which has only one record which is the running date table name:RUNDATE and field DATE and then looking at the arrival date and adding two days if applicable to return to the expression field LINEN "TODAY" LINEN: IIf([ARRIVAL]="([RUNDATE]![DATE]+2)";"TODAY";"2 DAYS") it returns 2 days to the other records which is fin...

I have a query that uses the dateDiff function to calculate the number of weeks between dates. The problem I am having is if the dates are less than a week apart how can I get the query to update my text field to 1. Thanks in advance. So you want any fraction of a week to be counted as a week? So 1 day or 7 days would be 1 week; 8 - 14 days would be 2 weeks, etc. Try: - Int(DateDiff("d", [EndDate], [StartDate]) / 7) Explanation of how it works: http://allenbrowne.com/round.html#RoundUp -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - h...

Dear sir, There is 1 data set is assumed running in A1 down, viz.: In A1 down is: 1,2,3,4,5,6,7,8,9, and 10 In F1 down is: 20, 7, 1, 12 and 8 And I have a excel forumula in the cell M1 as below: {=IF(ROWS(M$1:M1)<=SUM(--(COUNTIF($F$1:$F$22,$A$1:$A$10)>0)),INDEX($A$1:$A$10, SMALL(IF(COUNTIF($F$1:$F$22,$A$1:$A$10)>0,ROW($A$1:$A$10)-MIN(ROW($A$1:$A$10)) +1),ROWS(M$1:M1))),"")} And then I drag the formula from M1 to M5. Therefore, the formula will give me the result "1" in cell M1, "7" in cell M2 and "8" in cell M3. The formula is extracted t...

I tried to use the formula "IF" to find different rates, one rate is if the # is >= 2 but <5 and give me the error, please help me One way: =IF(AND(A1>=2,A1<5),rate1,rate2) or, if you have three rates: =IF(A1<2,rateA,IF(A1<5, rateB, rateC)) In article <72F19D81-14B0-41A7-963C-B158D0D7A62C@microsoft.com>, "nsnjlacm" <nsnjlacm@discussions.microsoft.com> wrote: > I tried to use the formula "IF" to find different rates, one rate is if the # > is >= 2 but <5 and give me the error, please help me I'm no...

Could anyone tell me if there is any requirement by Microsoft to first become a "certified partner" before being elligible for sitting in RMS - store operations certification exam? in simple words, Is there any Autorization code (given by micorosft to its certifed partners) required at the time of registring forthis course's exam? Or whether I can give this exam as an Independant IT professional? just like MCSD etc. I cant find this information anywhere and prometric testing centres at my locality do not have this information. Your timely help would indeed be appreciat...

::undefined::undefinedTo create a cash-flow spreadsheet of balance overdue by 30, 60 and 90 days respectively, as well as those sam increments upcoming in the future, I painstaking built the formula *=IF(AND(Y10>0,Y10<=30),X10,"")*, where X10 is the balance overdue b 0-30 days. The spreadsheet works fine because the columns for 31-6 days, 61-90 days, and >90 days each contain the appropriate values fo Y10, respectively. I copied and modified these four columns to eight to see who had futur balances by the same four increments. The resuslt is a clean spreadshee where eight ...

i have to calulate a Excel Spread sheet on a weekly basis so i know how to pay staff for product sales the file is output from a weird old dos program into a excel document I have 2 sheet inside a Excel document lets call them Sheet 1 and sheet 2 Sheet 1 has 3 columums Date Product and $Due etc 8/06/06 TABLE 8/06/06 CHAIR 8/06/06 TABLE $due Columum is not filled and i need this to auto fill Sheet 2 has a list of products in Columum 1 and 2 has the price on it PRODUCT $DUE Table $50 I need to do a if statement that if sheet 1 PRODUCT = SHEET 2 PRODUCT THEN sh...

Okay, this is hard to describe without taking a little while so pleas bare with me. I work for an airline ticketing company and we are tryin to combine the data from several spreadsheets onto one main spreadshee which i stupidly volunteered to do. There is one worksheet for each ticketer which list details of the far but the two imporant aspects for this problem are the airline code an the value of the fare. Now, at the moment the airline code appears in column A and ticke value in column D. This data is entered by each ticketer and it is no sorted alphabetically by airline but by date. So e...

Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream Hello, Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEARCH("cold",A1)),ISNUMBER(SEARCH("working&qu...