count the number of values when two conditions are met

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Worksheet
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - SEQUENTIAL NUMBERING OF WORKBOOKS when the question of how to get a template to number each new workbook that's opened, regular reference to the website: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html is made, but i copied the text, pasted it in my workbook's visual basic editor (opened by right clicking the sheet tab > view code) and nothing happenens. I did change what i shink i should (cell references, workbook name) and nothing happens. Please, Please can someone help me. Thanks Tag: count the number of values when two conditions are met Tag: 506064
  - 2
    - vlookup I have revenue numbers for 2007 and 2008 in columns, I need to do a vlookup (i think) that will classify into four columns demarking wih a check mark whether column 1 - the revenue increased, column 2 - revenue was new for 2008, column 3 - decreased and column 4 -if it decreased whetheth the business went away or sales were lower in 2008. I'm fustrated because I know what I want to do and know it's possible, but can't figure out what the formula is - help! -- Thanks, Jennifer Tag: count the number of values when two conditions are met Tag: 506062
  - 3
    - Returning a value from the same row using two variables Is there a way to return a value from a row using two variables from the same row? For example Account# Date Value 1 1/1/00 2 1 1/2/00 2 I'm trying to use vlookup but its not able to differentiate the same account number but different dates. I want to set a range on cells and have cell references for the account number and dates and just have the function return the value. Any help would be appreciated. Thanks! Tag: count the number of values when two conditions are met Tag: 506060
  - 4
    - Create a Macro with a List Hi Experts: > > I would like to build a macro with the List for multiple individuals that > will then enter the data from the List to the Summary Worksheet in the same > Workbook. This is the example of what I want to do.... > > List Sheet > > John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% > in Column D > > Put that data on the Summary Worksheet in Columns B, C, D & E > > I would also like to remove the % sign in Column D > > Can anyone help me with this? I would appreciate any assistance you can > provide. I'm working on a new reporting document that is using VLOOKUPs....a > lot of them every month....I think the Macro would be more efficient, but I > need your help to build on. -- jeannie v Tag: count the number of values when two conditions are met Tag: 506055
  - 5
    - result of formula posted in different cell I have a list of contacts that I would like to display contact info from a dropdown list Cell A4 - To: B4 (Drop down List) = Imnot Kim & the rest of the list Cell A4 - E-mail: B4 "Populate" Cell A4 - Phone: B4 "Populate" Cell A4 - Fax: B4 "Populate" This is what I got so far & it's not working (well it works for 1 name only) =IF(B4="Imnot Kim",Sheet1!F2) As soon as I drop to the next name i get "FALSE" Please help by the way I'm running WinXP & Excel 2003 Thnx, BadRasta Tag: count the number of values when two conditions are met Tag: 506033
  - 6
    - SUMIF formula while filtering I have the formula =SUMIF(K14:K267,E2,V14:V267) but want the number to only reflect when I filter by data in column H. Any ideas? Tag: count the number of values when two conditions are met Tag: 506024
  - 7
    - Cell Format - What's wrong? Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. Tag: count the number of values when two conditions are met Tag: 506023
  - 8
    - Alternative formula to the vlookup formula? I have data arranged like this: a b c d e f assembly Part No. price xyz1 xyz2 xyz3 TOTAL abc1 1 3 1 xxx abc2 2 2 1 xxx abc3 3 1 3 xxx xyz1 .4 xyz2 .5 xyz3 .6 Part abc1 is assembled with 3 pieces of xyz1 and 1 piece of xyz2. I would like to have the cells under "TOTAL" give the total price of that part including the assembly components so for part no. abc1, I want the price to show $2.70, part no. abc2, to show $3.6 and part no. abc3 to show 5.2. I am currently using a =sum(vlookup*c3, vlookup*d3, vlookup*e3) formula which works great except that there are so many assembly items that I cannot add any more functions to the cell. Is there a different forumla which would allow me to find the total price? If you need additional information, please let me know. Thanks, Victor Tag: count the number of values when two conditions are met Tag: 506021
  - 9
    - Excel drag down formula not working good.. hi, i have a formula =IF(E6="Casual",VLOOKUP('Product Detail'!L6,Types!D4:E14,2,0),IF(E6="Dress",VLOOKUP('Product Detail'!L6,Types!A4:B12,2,0))) when i drag it down to the rest of the cells i want only E6 to become E7 and grow each time i drag the formula down. the problem is when i drag the formula down it becomes like this: =IF(E7="Casual",VLOOKUP('Product Detail'!L7,Types!D5:E15,2,0),IF(E7="Dress",VLOOKUP('Product Detail'!L7,Types!A5:B13,2,0))) everything grew by 1 number. i tried putting $ signs before each letter but its not working for some reason. can someone find a problem here in my fomula or excel, am i doing something wrong? thanks for your time. Tag: count the number of values when two conditions are met Tag: 506020
  - 10
    - Excel drag down formula not working good.. hi, i have a formula =IF(E6="Casual",VLOOKUP('Product Detail'!L6,Types!D4:E14,2,0),IF(E6="Dress",VLOOKUP('Product Detail'!L6,Types!A4:B12,2,0))) when i drag it down to the rest of the cells i want only E6 to become E7 and grow each time i drag the formula down. the problem is when i drag the formula down it becomes like this: =IF(E7="Casual",VLOOKUP('Product Detail'!L7,Types!D5:E15,2,0),IF(E7="Dress",VLOOKUP('Product Detail'!L7,Types!A5:B13,2,0))) everything grew by 1 number. i tried putting $ signs before each letter but its not working for some reason. can someone find a problem here in my fomula or excel, am i doing something wrong? thanks for your time. Tag: count the number of values when two conditions are met Tag: 506019
  - 11
    - Excel drag down formula not working good.. hi, i have a formula =IF(E6="Casual",VLOOKUP('Product Detail'!L6,Types!D4:E14,2,0),IF(E6="Dress",VLOOKUP('Product Detail'!L6,Types!A4:B12,2,0))) when i drag it down to the rest of the cells i want only E6 to become E7 and grow each time i drag the formula down. the problem is when i drag the formula down it becomes like this: =IF(E7="Casual",VLOOKUP('Product Detail'!L7,Types!D5:E15,2,0),IF(E7="Dress",VLOOKUP('Product Detail'!L7,Types!A5:B13,2,0))) everything grew by 1 number. i tried putting $ signs before each letter but its not working for some reason. can someone find a problem here in my fomula or excel, am i doing something wrong? thanks for your time. Tag: count the number of values when two conditions are met Tag: 506016
  - 12
    - copy field settings of a field to other fields in same pivot tbl how do you copy the field settings of one field to another within the same pivot table? Tag: count the number of values when two conditions are met Tag: 506013
  - 13
    - Database function criteria In Lotus 1-2-3, you are no longer tied to a criteria range. You can embed a formula in the criteria field. For example, if I have a database named Calendar with columns titled: Number Name Weeks I can create a function like =DGET(calendar,"name",number=4). This eliminates the necessity of creating criteria ranges. Is such a thing possible in Excel? Art Tag: count the number of values when two conditions are met Tag: 506010
  - 14
    - excel quando estou a trabalhar em excel e tento mover o cursor para mudar de caixa, ele nao muda e o ecran Ã© que se mexe. qual a soluÃ§Ã£o? Tag: count the number of values when two conditions are met Tag: 506006
  - 15
    - Pivot Table Calculated Field using Grand Total I have a pivot table of sales data. I need to have a calculated field that gives me my sales mix. In an excel cell the formula would be =(2007%of Revenue)*(total2008Sales$) I have fields in the current PT that give me the % of revenue, and the sales numbers, but I do not know how to tell the PT how to find the total 2008 sales number (not just the sum of sales for that specific catagory) Hope this is clear... thanks Diane Tag: count the number of values when two conditions are met Tag: 506005
  - 16
    - Formula Errors / #Value! I made a template with my formulas and I have some lines that I need to put zero's in so I won't get #VALUE! where my formula is. For example: J16*K17 is the formula for L17. I have to put zeros in my J & K columns if I'm not using them or my L17 shows #VALUE! The first 5 or so lines do it but then the rest are fine. The other L cells just have zeros until I put in the figures that I want in the J & K. If I'm doing a quote and don't use all the lines, I get these errors on the unused ones. Hope I'm not being too confusing! Help!!! -- Mary Lou Tag: count the number of values when two conditions are met Tag: 505998
  - 17
    - sumif returns #VALUE! when linked workbook is closed I am using the sumif function to return values from a linked workbook. When the linked workbook is not open, however, the cells using this formula returns a #VALUE! error. Anyone have any suggestions to fix this? Thanks. Tag: count the number of values when two conditions are met Tag: 505997
  - 18
    - Can I pull in the filename used in Vlookup from another cell? I'm trying to link from 26 separate but identically formatted files to pull in similar data. I want to use a single formula, but swap out the filename based on the value I put into another cell. Anyone ever done that? I keep trying but can't get past the formula error checker... I was working with 26 different formulas but need this flexibility to adjust to the amount of data I need to collect from each file. Tag: count the number of values when two conditions are met Tag: 505996
  - 19
    - is there a solution to get the result in V? X= the max that can be given S= what clients already have T= what can be given to get the max (X) U= what is needed to get to the max V= is what is possile to give from what is over in T W= what is left from that wat kan be given (T) S T U V W X 40 123 35 35 88 75 20 88 55 55 33 75 10 33 65 33 0 75 11 0 64 0 0 75 10 0 65 0 0 75 45 0 30 0 0 75 30 0 45 0 0 75 2 0 73 0 0 75 15 0 60 0 0 75 6 0 69 0 0 75 Thanks in advance. Tag: count the number of values when two conditions are met Tag: 505994
  - 20
    - Create a macro with a list Hi Experts: I would like to build a macro with the List for multiple individuals that will then enter the data from the List to the Summary Worksheet in the same Workbook. This is the example of what I want to do.... List Sheet John Jones in Column A....123456 in Column B...ABCDF in Column C and 90.05% in Column D Put that data on the Summary Worksheet in Columns B, C, D & E I would also like to remove the % sign in Column D Can anyone help me with this? I would appreciate any assistance you can provide. I'm working on a new reporting document that is using VLOOKUPs....a lot of them every month....I think the Macro would be more efficient, but I need your help to build on. Thank You, -- jeannie v Tag: count the number of values when two conditions are met Tag: 505991
  - 21
    - I've created an Excel worksheet using the Vlookup command. I have created an Excel worksheet using the Vlookup command in a beginning cell. this is to determine what month I'm in and determine what the 1st day of the month is on. The next line I want to automatically continue down the page to insert the next following day, and finish off for each day for the month. But I can't figure it out. I hope that I've provided enough information to you. This is my table: number | name of mth | day in mth | week day 1 | January | 31 | Tue . . 12 | December | 31 | ??? LionHearted1952@msn.com Tag: count the number of values when two conditions are met Tag: 505989
  - 22
    - VEhicle Maintenance I know the date that a vehicle needs to go in for it's yearly maintenance and i want to see if i can cause the cell that the date is in to change color a month prior to the date....does anyone know if that can be done and how to do that? Tag: count the number of values when two conditions are met Tag: 505986
  - 23
    - SUM TOTALS AT BOTTOM OF WORKSHEET I seem to have lost my sum total ability on my worksheets that usually appears at the bottom right of the screen when you highlight a few cells to see what they add up to. Any help would be greatly appreciated :) Denny Tag: count the number of values when two conditions are met Tag: 505981
  - 24
    - Functions in Excel I am working with deveral worksheets. I have a excel spreadsheet on one tab and would like to be able to pull data from a source data sheet on another tab when I click in the cell. The cell contains a number which can be referenced to the source data, however I need to pull in a name from the source sheet and have it placed in an adjacent cell ont he schedule. I have tried Vlookup and it ddn't work. I have also tried the GetPivotInfo function but it only returns totals not the text I am looking for. Any suggestins as to what type of function will help? Beth Tag: count the number of values when two conditions are met Tag: 505977
  - 25
    - how can i stop formulas from displaying like this =R[-49]C+R[-25] it should look like =sUM(a1:a3), without all the brackets etc. Tag: count the number of values when two conditions are met Tag: 505975
- Next
  - 1
    - How can I create a check box in an excel template I have an order sheet template that I want to create check boxes that when cliked on insert a check mark into the box. Does anyone know how to create these? Tag: count the number of values when two conditions are met Tag: 505969
  - 2
    - VLOOKUP the right choice? Sorry for reposting but Still having trouble. Column A is (A2:A130), ascending, and is my Vendor list and is named "VendorList" and resides on a worksheet named "Lists". Column C (worksheet named "Lists") contains the addresses to the Vendors in column A and are aligned by rows. G12, on a worksheet named Purchase Orders, is a drop down list and is tied to "VendorList" on the worksheet named "Lists". When I make a selection in G12 (worksheet named "Purchase Orders"), I want G14 (same worksheet) to display the appropriate address. I am thankful for all your help. Tag: count the number of values when two conditions are met Tag: 505968
  - 3
    - Odd result when copying functions Usually, to post in this NG, I copy formulas from the function bar and paste them into a message here. However, IF I (in Excel 2007) Select Show Formulas Enter <alt-ENTER> into the formula so it will split over two lines in the function bar Copy the formula from the CELL (and not from the function bar) the result is as below: "=MID(A1,FIND("","",A1)+2,FIND(CHAR(1), SUBSTITUTE(A1,"" "",CHAR(1),2))-FIND("","",A1)-2)" If I copy the same formula from the function bar, then: =MID(A1,FIND(",",A1)+2,FIND(CHAR(1), SUBSTITUTE(A1," ",CHAR(1),2))-FIND(",",A1)-2) I might have thought this was by design, except that without the <alt-ENTER>, copying from the cell does NOT result in the quoting and double-quoting: =MID(A1,FIND(",",A1)+2,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),2))-FIND(",",A1)-2) Is this a bug? If so, can someone report it to the appropriate people? --ron Tag: count the number of values when two conditions are met Tag: 505967
  - 4
    - Looking Up the Answer by Row and Column I'm so new to excel that even though I know how to create some functions, I do not know the terminology, so I don't even know what to ask to get my answer. Bear with me. Imagine a table: Down the leftmost column is "Apples" "Pears" "Oranges", and across the topmost row is "Retail Price" "Quantity" "Our Cost". The rest of the table is filled with the appropriate numbers, so when i look at it I can easily see what "our cost" for "pears" is, or what the "quantity" of "oranges" are. But say I want it to be that easy for a spreadsheet user: Somewhere on the same worksheet I have two cells, one cell to enter the name of the fruit, the other cell to enter the property of the fruit (property may not be the right word). But the user enters "pears" in one cell and "our cost" in the other cell. The gears turn and out pops the appropriate response (say "$1.25") in another cell. How do I do that? Another table would be one that most of us remember seeing on maps: the distance between two cities. Along the left is listed a number of cities, and along the top is that same list of cities. To find the distance between "New York" and "Las Vegas" you simply had to finger across one city's row and down the other city's column until your fingers met and that was your answer. So how do I simply do this in excel if the user types in "New York" in one cell and "Las Vegas" in the other? Thanks. Tag: count the number of values when two conditions are met Tag: 505961
  - 5
    - Duplicate sheet, autonumber sheet, record data on another sheet Hi, I've had so much help in this forum over the last few days, i'm going to push my luck now. I have a master/template sheet, which needs to be copied, the new sheet autonumbered and certain data recorded. This is for quotes I have to give to customers on a daily basis. This is what i want to do: 1. First I want to be able to start on the template sheet everytime a new quote has to be opened. In this sheet is a field for document number. The new sheet has to add 1 to this number (initially) thereafter to the last sheet that was saved, in order to keep a sequence of quotes. How? What would be the simplist practical - to open a new sheet or workbook? If possible, I would want that new document number to become the tab name of the sheet. 2. Certain cells in every sheet need to be recorded in a different (summary) sheet where i can ceate a list with the details of clients we quoted for - say about 12 cells in every sheet need to be listed in columns. Please help Tag: count the number of values when two conditions are met Tag: 505959
  - 6
    - Question on breaking on cell into three seperate I am currently searching for a method to do a similar formula in excel. I currently process around 120 students every 2 weeks into classroom databases. Currently when we recieve the spreadsheet, the names of students show as: last, first middle initial in cells A1-A120. I was wondering if there was a way to create a formula to break this cell down into three cells one for first, one for last and one for middle inital. Example... Smith, John C. would be in cell A1 How could this be broken down into three seperate cells B1, C1 and D1? I tried basing this off the above suggestions but peoples name have different lengths so I need a way to seperate them by commas and periods. any suggestions would be appreciated. Thanks Tag: count the number of values when two conditions are met Tag: 505957
  - 7
    - Excel data formatting I would like to swap the data in an Excel spreadsheet by changing the columns to rows, and the rows to columns, For example: This: 1 2 3 Would become this: 1 4 7 4 5 6 2 5 8 7 8 9 3 6 9 Is this possible, and if so how can I do it? I have a large amount of data and performing this by hand would take an extreme amount of time. Any assistance you can provide would be appreciated. -- jreinier Tag: count the number of values when two conditions are met Tag: 505946
  - 8
    - Why Can't I navigate Excell 2003 Worksheets via Navigation Bar I can left click and choose a worksheet at the navigation bar but I cannot use the arrows to move either between worksheets or to the first or last worksheet Tag: count the number of values when two conditions are met Tag: 505942
  - 9
    - Function/Formula Question Example: I have a drop-down in A1 with 5 options (A, B,C,D,E) I need to be able to have a formula that looks at A1, and if it's value is "A" (from the drop-down) then place a "1" into cell B1. I am trying to perform this without a formula residing in B1 because a calculation is being performed on the value of B1. If the formula is there and the value is displayed, the calculation fails because it sees the formula instead of the value. The formula I use to display the value is: =IF(A1="A","1",IF(A1="B","2",IF(A1="C","3",IF(A1="D","4",IF(A1="E","5"))))) But, I need the end result to be a number that can be a part of another calculation. Help! Tag: count the number of values when two conditions are met Tag: 505940
  - 10
    - How to: If G12=A2 then C2...or if G12=A3 then C3 and so on 30 time How do I create a formula to have cell show the value in C2 or C3 or C4 etc...based on what is in A2 or A3 or A4 etc... EG: If (G12=A2 then C2)...or if (G12=A3 then C3)...or if (G12=A4 then C4) and so on as many as 30 times. Tag: count the number of values when two conditions are met Tag: 505931
  - 11
    - what does this forumla mean? I got this forumla: =SUM(IF(('[Title and Reg Chat Data.xls]FY08'!$C$2:$C$9000=W$66)*('[Title and Reg Chat Data.xls]FY08'!$E$2:$E$9000=$C67),'[Title and Reg Chat Data.xls]FY08'!$A$2:$A$9000,0)) I understand it is going to the worksheet title and reg, to worksheet fy08, looking at the range c2:c9000- what is the = w66? As well as the = c67? I know it is an if asking if those ranges = those cells, but what is the true part? and how does sum fit in? Thanks!!! Tag: count the number of values when two conditions are met Tag: 505929
  - 12
    - Number Format? I have entered the following in cell A1 ="Average "&AVERAGE(D:D) and the value I get is Average 47.247619047619. How can I round down to 1 decimal place? I have tried number format but it does not make a difference :-( Tag: count the number of values when two conditions are met Tag: 505923
  - 13
    - How can I find out what date going to be 3 month from today in ex If today is 08 NOV 2008 after Three or six months later what date going to be. How can I create a function in MS Excel. Can you please let me know. My e-mai is shahin512@hotmail.com or shahin512@gmail.com Thanks, Shahin Khan Tag: count the number of values when two conditions are met Tag: 505914
  - 14
    - Summarizing updated external data I have a basic sheet that refreshes external data based on what someone fills in for starting and ending dates in a couple of cells. The data is just a bunch of customer order lines that have the customer id, part, qty, price, etc. I want a variety of summaries of some of the data on the side of the sheeet or where ever, but I don't know the functions to use that will do somethnig like the following automatically.: Date --- Avg $ --- # of Ord --- Total$ --- Total Units 1/1/08 -- $15.00 -- 20 --- $500 ---- 300 1/2/08 -- $10.50 -- 10 --- $500 ---- 100 1/3/08 -- $12.35 -- 12 --- $500 ---- 250 Tag: count the number of values when two conditions are met Tag: 505913
  - 15
    - How do I create a staff roster in Excel? I have to create a staff roster each month for volunteers in different locations around the country. At the moment I had each persons name manually to the time cell relating to the hours they can work, eg, Mon 8:00 - 12:00, 12:00 - 16:00, 16:00 - 20:00 etc. Is there an easier way? I am currently running Office 2003 Tag: count the number of values when two conditions are met Tag: 505905
  - 16
    - GetPivotData Question I have the following GetPivotData formula =GETPIVOTDATA("Sum of ProjEstV.Value",'Validated Value Pivot'!$A$6,"Status Month","2008-04","Sum.OI Category","Other","OI Goal Category","OPS","CloseOIYr","","Status","Assigned") Which works fine. I just don't want to use the part "Sum.OI Category","Other" So I get =GETPIVOTDATA("Sum of ProjEstV.Value",'Validated Value Pivot'!$A$6,"Status Month","2008-04","OI Goal Category","OPS","CloseOIYr","","Status","Assigned") which gives me a REF error. WHY? Thanks, Barb Reinhardt Tag: count the number of values when two conditions are met Tag: 505904
  - 17
    - Lookup in dependent tables. I want to identify a unique part number for a light unit given that there are three variables associated with it. The variables are voltage, color, and steady/flashing. Each voltage has bulbs of different colors; each voltage and color has alternatives of steady or flashing. So I really need to 'nest' my lookups. However, I am not having any luck figuring out how to do it. The IF(AND ...) function looks likely, but I would have to make a very complicated formula. Seems I am making the atsk harder than I need to. In a way, I want to emulate the Online Autoparts application. When you have entered the Make (Ford Truck) the display changes and asks you for Year. Then it changes again and asks you for Body details. Finally it changes again and asks for Engine Size. Any suggestions? Many thanks. (Btw, I am using Excel 2003) Mike Tag: count the number of values when two conditions are met Tag: 505901
  - 18
    - Conditional Formatting Can someone help please! I would like to format cell colors based on values that are in the cell. The numbers in the cells are alpha-numeric order numbers followed by a number of days in the sytem (e.g. x12345678-14). Some cells have up to 14 "orders" in it. How can I conditionally format the cell if the number of days is between 7-14 (green), 15-24 (Yellow) or >25 (red). I am using Office 2007. Thanks in advance! -Rob Tag: count the number of values when two conditions are met Tag: 505900
  - 19
    - tab name please help how do i give a tab of a worksheet the same name as a cell in the worksheet, automatically Tag: count the number of values when two conditions are met Tag: 505899
  - 20
    - filers in more than 1 cell in excel i have a coloum F and it starts from F10 to F165 now how do i appile filters to them all i want to be able to use just use the drop down for the user to select what departmet they need and in the drop down their is going to be a list of 17 departments any ideas Tag: count the number of values when two conditions are met Tag: 505885
  - 21
    - Using roundup() but on a whole spreadsheet instead of since cells Hi, I need to update a fairly large spreadsheet so that all numbers are rounded up... using roundup() Ive looked around and ive come to the conclusion i need to use VBA. can anyone help? thanks. Matty Tag: count the number of values when two conditions are met Tag: 505882
  - 22
    - sheet name is it possible to force a cell enty as a sheet name, i.e. i have a worksheet that i use for quoting for clients. i wrote a macro that automatically put a new number on each sheet when it duplicate the master sheet. i would like that quote number in the worksheet, to be used as the sheet number for easy reference Tag: count the number of values when two conditions are met Tag: 505879
  - 23
    - Exceel formulae hi there!! Am New to this discussion and its this problem that brought me to this discussion.... its have got to adjust values in Col E to 30, if it exceeds in Col D where Col D is sum of A, B and C..... tried alot of rubbish formulae but all in vain... can anyone help plz -- arisan Tag: count the number of values when two conditions are met Tag: 505878
  - 24
    - Comparing 2 dates and times Hi I have a spreadsheet with 5 columns: A = date an e-mail received B = time e-mail received on that date C = date e-mail replied to D = time e-mail replaied to on that date e-mail E = was the e-mail replied to within agreed time limit? The agreed time limit is 24 hours. I'd be happy enough coming up with a formula to input Y or N into colmun E, if I only knew how to subract a time AND date from another time and date. Can anyone advise, please? Many thanks. Keith Tag: count the number of values when two conditions are met Tag: 505877
  - 25
    - Menu options When using excel some menu options that are not used frequently eventually are hidden by the system until you expand the menu, however when an option is used once that option is displayed again until the expiration of the display period and then it is hidden again. I want to hide options that are used infrequently, either by reducing the waiting period to hide again or by setting the option to hide manually. This functionallity would greatly improve my use of the product by using keystrokes rather than pointing with a mouse. Any ideas are greatly appreciated. Luis Elguezabal Tag: count the number of values when two conditions are met Tag: 505876

Hi there,

I am struggling with an Excel Formula:

On one column I have the hotel contract status: Signed, cxl, in nego.
On the other column I have the location of the hotel: City Center, Airport,
Far away.

I want to calculate how many contract signed are in the city center, and I
have tried sumif, countif, but can't seem to get the proper result...

Help!!!

Top

RE: count the number of values when two conditions are met by MikeH

MikeH
Fri May 09 03:33:01 CDT 2008

Hi,

Try this

=SUMPRODUCT((A1:A20="Signed")*(B1:B20="City Center"))

Mike

"Sophie D." wrote:

> Hi there,
>
> I am struggling with an Excel Formula:
>
> On one column I have the hotel contract status: Signed, cxl, in nego.
> On the other column I have the location of the hotel: City Center, Airport,
> Far away.
>
> I want to calculate how many contract signed are in the city center, and I
> have tried sumif, countif, but can't seem to get the proper result...
>
>
> Help!!!

Top

RE: count the number of values when two conditions are met by SophieD

SophieD
Fri May 09 03:45:03 CDT 2008

AWSOME!!!

Mike you just saved the day!!!

Thanks soo much!!!

Top

RE: count the number of values when two conditions are met by MikeH

MikeH
Fri May 09 03:50:01 CDT 2008

Your welcome and thanks for the feedback

Mike

"Sophie D." wrote:

> AWSOME!!!
>
> Mike you just saved the day!!!
>
> Thanks soo much!!!

Top