COUNTIF: seems easy but....

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- 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
    - Scan and remove column data query Hi, I have a spreadsheet (http://iedmont.blogspot.com/) and am trying to find a solution to scan column A for duplicates and if found, remove the cell contents of the active cell, column B, column, C and column D. So as an example in the example sheet, it would scan column A for data1, leave cell contents of A2, B2, C2, D2, E2 and F2 in place and remove cell contents A3 to D5. Then keep all cell contents in row 6 (only one entry found for data2). Then keep all row 7 cell contents and remove A8 to D13, leave row 14 and finally leave row 15 and remove A16 to D16. All data in columns E and F to remain. Can anyone help please? Many thanks. IE. Tag: COUNTIF: seems easy but.... Tag: 116622
  - 2
    - Hyperlinks to '[workbook]Sheet!'Range(Cell) Dear fellows I'm struggling with my hyperlinks. Do anyone know the hyperlink-code/reference to a specified sheet and cellreference in another workbook? I want my hyperlink to open this other workbook somwhere in my filestructure, and activate a specified sheet/cell in this workbook. Is it possible, and how? I'll appreciate your kindly help Regards Nils Morten Tag: COUNTIF: seems easy but.... Tag: 116620
  - 3
    - push rather than pull cell data. Is it possible with excel to push data from cells to another sheet in same excel file rather than pull it from the cells you want populated each time cell data is changed. Thanks for any help with this. Tag: COUNTIF: seems easy but.... Tag: 116618
  - 4
    - A way to identify and delete duplicate entries? A couple times I week I run a report on accounts (account numbers are either 7 or 8 digits long) looking for 3 different issues, import the results into Excel, and distribute the results to be worked on. Since the same account may have been identified by two or more of the search parameters, the final report may contain anywhere from 5% to 15% duplicate (sometimes triplicate) entries. Since it's a bit of a time-waste to even take a look at an account that may already be in the process of being resolved, is there an easy way to identify and delete duplicate entries? Even just identification would be great, as I could manually delete. Thanks for your help. -- Alan Tag: COUNTIF: seems easy but.... Tag: 116612
  - 5
    - Simple formula help please... This should be simple...but it's beyond my knowledge. I have column A (20 Drivers) Column B (position in race 1-20) I want column C to show points won based on the cell contents in adjacent cells in column B. Where first place (cell entry=1) gives 10 points, second (2) gives 8, third gives 6 down to 1pt for eigth place. 9th and below should give zero points. What formula should I be using in column C? Thanks for any help. Tag: COUNTIF: seems easy but.... Tag: 116601
  - 6
    - Excel 07 + iSeries Excel Add-In crashing I am test bedding Vista and Office '07 with our legacy programs. As our main databases reside on an AS/400, we extract considerable data for reporting by way of the iSeries Access for Windows Excel Add-in. This has always worked fine in previous versions of Office. However, in Excel '07, when attempting to run the connection, the program crashes. The add-in works properly up until the point when the data is to be virtually transferred to the Excel sheet. (You can see the libraries and files, make field selections, and play with the data options etc.). Running the transfer outside of Excel works fine. Office diagnostics can provide no info or help. Program info as follows: Microsoft Office Excel 2007 (12.0.4518.1014) MSO (12.0.4518.1014) IBM iSeries Access for Windows V5R4M0 SI25949 Error Event as follows: Event 1000 - Application Error: Faulting application EXCEL.EXE, version 12.0.4518.1014, time stamp 0x45428263, faulting module EXCEL.EXE, version 12.0.4518.1014, time stamp 0x45428263, exception code 0xc0000005, fault offset 0x00a14457, process id 0x107c, application start time 0x01c7625a54ed8fd2. Anyone with any ideas? Thanks in advance for your time. Ron McIntyre Montreal, Canada (Cross posted from microsoft.public.office.misc ) Tag: COUNTIF: seems easy but.... Tag: 116592
  - 7
    - Create a list and use drop down in other worksheets..possible? Hi, I've search on how to do this task and found things similar but nothing exact. What I would like to do is create a 'list' of entries in one worksheet and then in another using a dropdown box select any entry from the 'list'. For example. Worksheet 1 A1 A2 A3 A4 ....etc Orange 100 0.5 17 Apple 88 7 23 Worksheet 2 I would like to be able choose from a drop down (combo box?) Orange and have the other cells (A2,A3,A4) follow suit in their respectable columns. My goal is to input all the food data in one worksheet and be able to select what I eat on a daily basis from the list in another worksheet, calculating calories/fat,etc. This way I can enter info once and just select from it instead of C&Ping or re-entering the same information all the time. TIA Jeff Tag: COUNTIF: seems easy but.... Tag: 116581
  - 8
    - Protecting data validation Drop DownColumn I made a column which displays only dropdown lists made from datavalidation. When i copy and paste cells over it the dropdowns are no longer available. Is there a way to protect the column ? thx Tag: COUNTIF: seems easy but.... Tag: 116577
  - 9
    - looping through columns and rows Hello, Can someone show me the code which would allow me to search through a number of rows and columns, to identify a criteria. Each time the criteria is found, 1 is added to a variable. When the variable reaches a set number, the search moves to a different column. This would allow me to allocate people to jobs according to their first, second, third choice etc, without resourcing too many people to a job. can you help? I have very little experience, but am usually able to see what is happening in the code and expend on it to take care of more elements. kind regards, Matt Tag: COUNTIF: seems easy but.... Tag: 116575
  - 10
    - weird behavior when checking check boxes Hello ! I have a really weird problem. I have a excel file with about 20 sheets. on one, I have check boxes. about 10 when I check one box, the others grow in size. when I click on one that is bigger, it becomes smaller and the others become bigger. Have you ever seen that behavior ? it is the text size and the small box that grows or become small thanks for your help. tiah. PS : Excel 2003. Tag: COUNTIF: seems easy but.... Tag: 116571
  - 11
    - Leading Zeros are lost when importing spreadsheet data I am importing data from an Excel spreadsheet into a .Net DataTable using the following code: // conn string Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties="Excel 8.0" OleDbConnection conn = new OleDbConnection(String.Format(this.ExcelConnString, ofd.FileName)); OleDbCommand cmd = new OleDbCommand(this.SpreadsheetSelect, conn); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataTable dtAHMOrders = new DataTable(); da.Fill(dtAHMOrders); The data comes across fine except for zip codes with leading zeros in which case the leading zeros are lost. I have tried every exampe I've found with the following results: When formatting the column as Special>ZipCode the data displays fine in the spreadsheet but the leading zeros are stripped. When prepending the zip code with an apostrophe the data displays fine in the spreadsheet but the leading zeros are stripped. What is completely frustrating me is that I have a different spreadsheet which also has leading zeros on zip codes and they import perfectly!!! When inspecting the Cell Format of this spreadsheet the cells are formatted as simply General. When looking at all of the cells show the green wedge in the upper left corner of the cell and the error says Storing Number as Text. Uppon closer inspection of the data I find no apostrophes, or spaces, or carrats, or ANYTHING! If I open a blank spreadsheet and enter 00401 into a cell, which is formatted as General by default, the leading zeros are lost as soon as I leave the cell. Can ANYONE shed some light on this? Thank you. Tag: COUNTIF: seems easy but.... Tag: 116563
  - 12
    - Subtotal Average excluding zero I found how to do an average excluding values that are zero For example: =AVERAGE(IF(A1:A10=0,"",A1:A10)) but I'm not sure how to do this when I need to use subtotal because the sheet has the autofilter. I use =subtotal(9,A1:A10) to get a subtotal that changes when the filter changes. And I know there is an average =subtotal(1, A1:A10), but how do I do a subtotal average excluding zeros? I'm using excel 2007 Thanks, Mark Tag: COUNTIF: seems easy but.... Tag: 116559
  - 13
    - How do I Lookup and SUM multiple "finds?" How do I Lookup values from another sheet, knowing that there will be multiple occurances of those values, and sum the offset cells? For example: Lookup value is: BAKER Table is: SMITH BAKER JONES BROWN BAKER HODGES 4 5 6 3 8 10 7 5 9 56 3 1 I want a forumla that uses the Lookup "Baker" and SUMS the second row of 5+3 to yield 8? Can I do this w/out using VBA? (He asked hopefully...) Thanks for any insight. Russ Tag: COUNTIF: seems easy but.... Tag: 116557
  - 14
    - Importing text from an Explorer window Howdy all. OS: XP, Excel 2002 SP3 I have my music stored in the My Music directory. I have Artist, Album Title, etc. attributes turned on. What I'd like to do is to take all of this information and input it into an Excel spreadsheet. When I "Select All" from the window, and "Copy", it wants to copy the actual songs, and not the text. Also, the "Paste" option in Excel is grayed out anyway. Is there a way that I can treat the info in a Browser window as just text, and then copy that info into a spreadsheet? TIA. -- Don Cox Tag: COUNTIF: seems easy but.... Tag: 116555
  - 15
    - auto update of conditional macro Hi again, I have just noticed that when I open the spreadsheet the cell with the =today() command automatically updates but the macro that is dependant on the answer does not update. I have tried to use refresh F9 but that doesn't work. I can go into the cell and hit enter and that then refreshes the colour assigned to the day. Is there a way of doing this when the spreadsheet opens up? Or is that not advisable? Andrew Tag: COUNTIF: seems easy but.... Tag: 116546
  - 16
    - Reducing data points? I have 4000 data points of an electrical device. I'd like to use every 20th point to run math on. How do I tell Excel to skip 19 data points then get the 20th and skip another 19 and get the 20th etc.? Thanks, Mike Tag: COUNTIF: seems easy but.... Tag: 116544
  - 17
    - conditional formatting I only just discovered the possibility of conditional formatting. Apparently, only three formatting rules can be set. What is the way around this limitation if I want to make it more complex. Some formula I guess? Thanks. Tag: COUNTIF: seems easy but.... Tag: 116543
  - 18
    - Excel Sum with Filter & Copy specific Data Hello, I have two questions regarding Microsoft excel. If you could help me I'd really appreciate it! QUESTION #1 I have 2 worksheets. One has a list of ID numbers (unique - one of each) and a column for totals (which is blank as of now) WORKSHEET1 A B ID #: Total: 1 2 3 4 5 The other has a list of ID numbers (many listed multiple times), has a number entry which I need to be summed and a date (which I filter by to get a range) WORKSHEET2 A B C 1 345 Jan 1 2 327 Feb 10 1 745 March 22 1 237 Dec 6 4 845 Jun 3 3 23 Aug 7 2 2222 Jan 20 What I'm wanting is the entries for each ID to be summed together, but only for the data that is visible after filtering. Thus, the sumif function won't work as it also adds the filtered-out data. I can't get the subtotal function to work either because I only want specific data corresponding to the individual ID to be totaled. What I'm hoping the result to look like: WORKSHEET1 A B ID #: Total: 1 345+745+237 2 327+2222 3 23 4 845 5 0 [or blank] ...except I want just the final resulting total...not all the xx plus xx written out ;) QUESTION #2 Have 2 Worksheets One looks something like: WORKSHEET2 ID Name Pattern Colour Number 1 Bob Stripe red 547 2 Jill Square red 322 3 Joe Circle blue 0 4 Jack Plaid red 0 5 Paul Curly blue 77 WORKSHEET1 Right now Column A and Column B are blank for now. In column C there are numbers that will be linked via vlookup to each name (don't worry I can do this ;) ) When complete it should look like: Name Colour VlookupNumber Bob red 12 Jill red 3 Paul blue 4 TOTAL =sum(xx) I need to input just the Name and Colour of each person into the second chart for those people who have a number greater than 0. I'd like the Name in column A and the Colour in column B for those entries. As someone else will be using this file I'm trying to make it as user-friendly as possible. Is there a way to make it automatically update when one of the numbers are changed, or a new ID/Name is changed/added? Finally, after all the entries I want a TOTAL row, that just totals up the vlookup values on the row after the last Name. While this seems like an easy task (=sum(C2:CXX) ) the number of Names in column A will be changing over time, so it will have to move. Is there any easy way to do this? If anyone could help me with one or both questions, I'd greatly appreciate it. Thanks!! Tag: COUNTIF: seems easy but.... Tag: 116530
  - 19
    - inserting a hard return with replace in a cell Is there a way to insert a hard return or line break using the replace function in a cell? Thanks brent Tag: COUNTIF: seems easy but.... Tag: 116526
  - 20
    - default template in Excel 2007 I know that I can save a template in 2003 with the name "Book.xlt" in the XLStart folder and it will become the default workbook when starting Excel. This does not seem to work in 2007. Does anyone know how to do this? Tag: COUNTIF: seems easy but.... Tag: 116524
  - 21
    - Compile error in hidden module: distMon Every time I open my excel I get "Compile error in hidden module :distMon" Tag: COUNTIF: seems easy but.... Tag: 116523
  - 22
    - How can I replace a value?? in a column the following formula Sum(R6:R7) Sum (R7:R8) Sum (R8:R9) goes to Sum(R19:R20) I need to replace the first R value with R6 all the way down. Is there an easy way to do this, other than changing each formula individually? Any help apprecited Skip Tag: COUNTIF: seems easy but.... Tag: 116513
  - 23
    - Lotto Formula My apologies. I posted this question yesterday and the response didn't work (or I don't have the knowledge to make it work). Regardless, I will post again and I'm hoping someone can help me out. I am setting up a Lotto chart for work. The game we're playing is Keno and there's a daily draw. The initial investment is $250. Column A: Will have the daily investment. Column B: Will have the winnings. Column C: Will have the losses. Column D: Will have the actual amount that's left from the initial investment and it will take into consideration the win or loss for that day and also the amount from the day before. For example; If the day before we have $230 in column D. Then the current day will take the total of column D (the day before), subtract the daily investment and either add or subtract the win or loss respectively. May someone help me with this formula? I would greatly appreciate it. Thanks to Jan who sent me =$A$2+SOM($B$2:B2)-SOM($C$2:C2) but once again, I couldn't get this to work. Tag: COUNTIF: seems easy but.... Tag: 116511
  - 24
    - hyperlink or what? I know I can create a bookmark in Word and then insert a hyperlink in Excel such that when you click on it, it launches Word and navigates to that location. Not bad. I've also found that I can copy the relevant Word section and embed the object in Excel. Pretty cool. Closer. What I want is the "look" of the embedded object (looks like the Word document) but I want it to pull directly from the document - not from the static thing that was embdedded. I want it to display "real time" so that any changes to the document are reflected each time the worksheet is opened. Can that be done? thanks Tag: COUNTIF: seems easy but.... Tag: 116505
  - 25
    - Line break function? I have the following function ="A"&"B"&"C" which gives: ABC Is there a way to insert line breaks so that instead of displaying ABC, it will display: A B C Tag: COUNTIF: seems easy but.... Tag: 116504
- Next
  - 1
    - Issues with 3-Column Sort I have a user that is having a problem with a spreadsheet when doing a 3 column sort. We are using Excel 2003. Below is a description of the problem and what we have tried so far. If anyone has any ideas, I would great appreciate them. Thank you, JW Problem: Can not do one particular 3-column sort. Selecting to sort by column A, then by B, then by C does not work. Sorting by A itself works. Sorting by B, then by C does. I have it narrowed down to if we include column A in a multiple column sort, it fails. What we have tried: 1) Copied the cells from one sheet to another. The problem follows to the new sheet. 2) Manually keyed the data into a new sheet. The multiple column sort works. At this point, we felt that there must be a control character hidden in Column A somewhere? 3) Copied the 3 columns from Excel and pasted into Notepad to remove the formatting. Imported the text file into a new sheet. The multi- column sort failed. Has anybody seen this type of problem before? Very strange. Let me know if more information is needed or if anyone has any ideas. Thanks! Tag: COUNTIF: seems easy but.... Tag: 116499
  - 2
    - Importing .txt data into existing spreadsheet without overlaying formulas greetings - can anybody tell me how to import text data into an existing spreadsheet without overlaying formulas in some columns? (need to skip the overlay of those columns that just have formulas used in calculations from data in other columns) regards, booray Tag: COUNTIF: seems easy but.... Tag: 116495
  - 3
    - Scheduling staff depending on their seniority and choices Hello, I need to create a spreadsheet which assigns staff to specific roles and shift times. The staff are allocated on a seniority basis. The most senior person is aligned to his/her first choice of shift/task, then it moves to the next person. So far I have listed all staff and their seniority and added five choices for each person. I need to run a macro/code to assign the staff, and check to ensure that once the shift/task is full, the shift/task is closed off. The shift/task choice is a a simple code (i.e. A2 means shift A task 2). Can anyone help me? yours hopefully, Matt Tag: COUNTIF: seems easy but.... Tag: 116494
  - 4
    - Staff Rota Hi All, I'm trying to create a spreadsheet to automatically perform staff rotation around rides in an amusement park. I have a list of staff (A3-A30) I have headings of rides along D-N, and the dates that they were last on that ride in the appropriate boxes. B column states if they are in that day or not I want C to say what ride they are due on, only if they are in on the day Having real problems with the forumlas, its been 3 years since I last used Excel. Many thanks all. Tag: COUNTIF: seems easy but.... Tag: 116492
  - 5
    - Launch Excel cleans the clipboard If I need to copy content from another program to Excel, I have to launch Excel before doing the copying (Control-C) or the clipboard is cleaned up after launching Excel. Is there a way to change that behavior? Thanks, Tag: COUNTIF: seems easy but.... Tag: 116491
  - 6
    - Transfer text from Access table into Excel Hi All, I'm attempting to transfer a date I have stored in a table in Access, into Excel. The export works fine, however when I view the date in Access, it is presented in the format 'dd/mm/yyyy Hence, this is not recognised as a date in Excel and referenced sheets to this cell don't display correctly. I can manually get rid of the ' character, however I'm trying to implement a macro that would do this - I used a standard search and replace macro, however it can not find any ' characters. When I manually use Search and Replace, it also is unable to locate this character in the entire spreadsheet. Does anyone have any suggestions of how best I could work around this..? Cheers Tim. Tag: COUNTIF: seems easy but.... Tag: 116488
  - 7
    - Help! with code to open word I need to write a macro that opens up word & copies a selection from the excel file into the new word document pasted as "Picture (enhanced metafile)" I am currently using excel 2003 Tag: COUNTIF: seems easy but.... Tag: 116487
  - 8
    - Problem reading Excel 2003 file through OleDB Hy everybody, I've got a problem reading an Excel 2003 file in VB.NET. The problem comes when I do a SQL sentence through the Jet engine. If one column in the Excel file contains some numeric values and some string values, only returns the types of the first row that finds, the other data type is returned as NULL value. How can I return al the data types from the column? Here is my SQL sentence: SELECT * FROM [PAGE1$] Thank's. Tag: COUNTIF: seems easy but.... Tag: 116485
  - 9
    - Excel problem reading data when aflanumreic field is numeric Hi, i'm facing the following probem when i import records from an excelfile by code (i use OleDbDataAdapter and OleDb.OleDbConnection provider=Microsoft.Jet.OLEDB.4.0;.....Extended Properties=Excel 8.0): When an alfanumeric field contains numeric data (like "1234556" the data is imported as "<NULL>", but if this alfanumeric field contains data like "AB123456" the value "AB123456" is imported. I want to know how i can solve this because the data read is very critical, the null-values give wrong outcomings. Thanks for yor effords. Greetings, Verhaad Tag: COUNTIF: seems easy but.... Tag: 116482
  - 10
    - Adds-ins/tools for publishing Excel data as web pages? Can anybody suggest some low cost tools/add-ins for publishing Excel workbooks and sheets as HTML web site content? The data includes tables and charts. The output should support tabs for different sheets. This is for a weekly progress report. Until now I've been using a wiki, but I'm fed up with the limitations and increasingly arcane formatting syntax. The source data is in Excel. Tag: COUNTIF: seems easy but.... Tag: 116480
  - 11
    - date formula Hello, In cell A3, I have today's date (March 18, 2007). I would like to copy that cell down maybe 100 times. I would like each cell to be updated by 1 day. I appreciate any help I receive. Thanks. Tag: COUNTIF: seems easy but.... Tag: 116470
  - 12
    - Default Disappearing Text Is there a way, in Excel 2003, to have a template with default text in the cell that disappears once the cell is entered? For example, in the form I am creating, one of the cells to be filled is Event Name. Is there a way to have "Event" appear in a cell such that it disappears when I click on the cell? It would be helpful if the default text is non-printable so that it does not show if the document created from the template is printed and not all of the cells are filled with new text. Tag: COUNTIF: seems easy but.... Tag: 116468
  - 13
    - pivot tables Can't understand why it's so difficult to create a pivot table report. I've followed all the tutorials I could find on google-including Microsofts', none of them work for me. Isn't there a simple way? It's so easy with MS Works. Tag: COUNTIF: seems easy but.... Tag: 116459
  - 14
    - File association oddity On my machine, I have both Office 2002 and 2003, which are loaded into different folders. Xls files I open from Windows Explorer open in Excel 2002. I changed the association to 2003, but something odd is now happening. Now, when I double-click an xls file from Windows Explorer, the correct Excel version opens (2003), but upon opening, it first alerts me it cannot find several other workbooks and I have to click OK to each. I think it's pulling them from an old recent used list or something. Anyway, the desired file finally opens after all that. Excel opens fine when I start it normally. Did I do this wrong, or was there a better way to change association to 2003? These were my actions to change association: In Windows Explorer Folder Options, under File Types, I located the xls extention and clicked the Advanced button. I then clicked the Open action and clicked the Edit button. There, I changed the path in the "Application used to perform action" field. (I only changed the path, not the file name or switches.) This is what it reads: "C:\Program Files\Microsoft Office 2003\OFFICE11\EXCEL.EXE" /e %1 DDE Message: [open("%1")] Application: Excel DDE application not running: [blank] Topic: system Thanks, Chuck Tag: COUNTIF: seems easy but.... Tag: 116458
  - 15
    - Can you search for a value output by a formula? I want to search for a value in a cell that comes from a formula. for example, if I have =A1*A2=15.58 in cell A3. I like to do a search for 15.5 and arrive at cell A3. Is that possible? thanks. Tag: COUNTIF: seems easy but.... Tag: 116455
  - 16
    - chart SLOW when axis range fixed Create a new scatter plot with ~2000 samples of RAND(). Observe how quickly they're drawn... Do a 'format axis' on the vertical axis and pick "fixed: 1.0" for the maximum value, instead of the default "automatic: 1.2". Now click on one of the data points or do a 'format series' and wait for a few minutes while Excel uses 100% cpu. Has anyone else run into this? Is there a workaround other than "don't change the maximum value of the y-axis"? Tag: COUNTIF: seems easy but.... Tag: 116452
  - 17
    - How to Select Multiple Cells Conforming to A Certain Criteria Automatically? Dear all, I have a big table containing a lot of data in my worksheet. Within a particular column, I want to select cells whose row numbers form an arithmetic progression with a common difference 8(e.g. G6, G14, G22, ...). At the moment, I can only perform this task by selecting the cells individually while pressing "Ctrl", however, due to the large number of data in the table, this work is very tedious. Does anyone know if there is an automatic way to select these cells? Thank you. Best wishes, Tag: COUNTIF: seems easy but.... Tag: 116448
  - 18
    - Changing font within cell using VBA I have a VBA macro in Excel 2003 that puts a word within text already in a cell. I want this word to appear as normal font when the rest of the text in the cell is in bold. How can I define the font of a substring of a cell? Cheers Mark Tag: COUNTIF: seems easy but.... Tag: 116441
  - 19
    - Formula Help Please I'm doing a lotto chart for work. The initial investment is $250. If we win or lose, I would like to key in the amount in the respective column and have the amount column automatically update itself. The one thing I can't figure out is how to take the previous days amount and make it part of the formula. Any help is greatly appreciated. Thanks. I don't know if the example chart I've put in will translate well on the upload but this is how the chart looks. Thanks once again. Initial Investment Win Lose Amount 250 10 240 10 250 153 353 30 323 Tag: COUNTIF: seems easy but.... Tag: 116439
  - 20
    - Excel07: where did Draw Object Fill Patterns go? Did the ability to fill drawing object with patterns just go away? I see pictures and textures, but no patterns. Losing these is a big deal for things like engineering sketches and things that have to be faxed--where none of the textures, say, work at all well). Tag: COUNTIF: seems easy but.... Tag: 116438
  - 21
    - Adding a button with a function on protected sheets. How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks Tag: COUNTIF: seems easy but.... Tag: 116435
  - 22
    - Formula,help please. Hi all, I've tried to insert a formula to cell,but it seems I can't figure it out.I want a formula that results like below If cell C7 = 0 it result is 0 , if C7 is 1 untill 6 it result 1, if C7>6 it result =1. How does to make it works.Please advise Tag: COUNTIF: seems easy but.... Tag: 116433
  - 23
    - Excel --> powerpoint Hi I would like to know if it's possible to write a macro which - open excel file - select an area or graphic - copy these area or graphic - paste it on a slide - select another graphe - paste it on an other slide and so on .. Thanks BM -- (o\---/o) ( , , ) _____,--.(_(Y)_),--._____ | "~~" "~~" | "If something is so complicated |~@~ Benoit Moreau ~@~| that you can't explain it in ten | benoit@be.tf | minutes then it probably isn't | | worth knowing anyway." |_________________________| - Calvin & Hobbes ) | ( (___,'.___) Ecologie : ce mail a été transmis avec des électrons 100% recyclés Tag: COUNTIF: seems easy but.... Tag: 116430
  - 24
    - Count condition true for 3 lookup columns Hi All, I have a worksheet that contains 3 seperate lookup columns. My challenge is that I need a low overhead way to find and count the combination of true lookups. So if one of the 3 looksup is found I would get a return of '1', if 2 of the 3 are found then '2'. I know I can do this with an array formula, but am worried about the calculation hit as this is a very complex worksheet and already has a long intitial calc time. I suppose I could do the inverse and could an 'iserror' condition and then subract that answere from 3. I thought of using a complex 'if' statment, but the logic seemed like it would be too complex. Any suggestions or am I stuck with an array formula? If so could someone suggest a sytax to start with. Thanks, Greg Tag: COUNTIF: seems easy but.... Tag: 116428
  - 25
    - Need help on this....thanks I have an Excel worksheet which stored a survey outcome of over 1200 members. There are over 30 questions in the survey and the first column captured the member ID. The problem is 3 of the survey questions that allow multiple answers, instead the answer for those three questions are captured in seperate column, the answers are stored in a single column and it makes it very difficult to analysis those result. As a result I created addittional columns depending on the total number of the answers available for that question and assign the result manually to that column. It is very time consuming and I wonder if I can create a macro to loop through each member on those three questions and assign the result(s) to the new created columns. The format of the existing Excel worksheet is as follow; * denote as multiple answer allowable for example: Q2 has 4 answer choices, car, plane, boat and people and in the order of the answer in the survey Q3 has 5 answer choices, power point, excel, access, word and outlook and in the order of the answer in the survey memberID----Q1-------*Q2-------*Q3---------Q4 123456 yes car power point yes plane excel access word 234578 no plane excel no boat word people 784528 yes people power point yes access outlook What I did to the existing Excel worhsheet I changed the column name from *Q2 to the first available answer which is car for the Q2 and added three addittional columns, plane, boat and people next to the column "car" and so on for *Q3 as follow: memberID----Q1----car----plane----boat----people----powerpoint----excel----access----word----outlook----Q4 123456 yes car plane power point excel access word yes 234578 no plane boat people excel word no 784529 yes people powerpoint access outlook yes What I want to achieve here is ONE ROW of record for each of the member survey answer. I need a macro to loop through each member and assign the survey result to the proper column and delete the "Blank" rows at the end. Thanks. Tag: COUNTIF: seems easy but.... Tag: 116426

Hello,

how can I use the countif function to count the number of star (*) in a
range ?

If I do =COUNTIF(B1:B8;"=*"), I have the number of cells because * is
understood as a wild card...

Thanks in advance

Laurent

Top

Re: COUNTIF: seems easy but.... by KL

KL
Wed Mar 21 03:51:58 CDT 2007

if you want the number of cells that contain * then:

=COUNTIF(B1:B8,"=*~**")

if you want the number of * in all cells then:

=SUMPRODUCT(LEN(B1:B8)-LEN(SUBSTITUTE(B1:B8,"*","")))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

"Laurent" <paucahrdl@hotmail.com> wrote in message news:2C49A41E-B22F-4C5F-B547-995BE2FE81F3@microsoft.com...
> Hello,
>
> how can I use the countif function to count the number of star (*) in a
> range ?
>
> If I do =COUNTIF(B1:B8;"=*"), I have the number of cells because * is
> understood as a wild card...
>
> Thanks in advance
>
> Laurent
>

Top