Wondering if someone could help with this problem After doing a vlookup and then cleaning up with pastespecial/values I am finding that the visually empty cells are still being counted in my counta totals I have tried this subroutine to clearcontents on the empty cells, which works if I do it manually. Sub clearempty() For i = 3 To 588 If Cells(i, 14) = "" Then ActiveCell.clearcontents End If Next i End Sub Can anyone tell me 1. why my clearcontents subrouting is not clearing and 2. is there another way to clear out this invisible data i...

I have a table called 07DATA which has two fields in it: DOLLARSPAID and DIRECTION. Dollarspaid is clearly a currency field which has multiple dollar values. Direction is a field which contains either an "I" or an "O". What I'm trying to do is to create a query with two fields that shows the total dollars paid for both "O" and "I". I can easily do this by creating separate queries and then a third query that returns the results but there must be a way to do this in just one query? Thanks in advance! SELECT IIf([DIRECTION] = "I"...

Why is that my answer to my formula will not update? It was working fine at first not it do not. I want to count text in each cell (eg. A5-A9). The formula I use is =COUNTA(A5:A9). My answer will be 5. When I delete a row the answer do not update. Why? Press F9 to do a manual recalculation. Did that work? Then you worksheet is set to manual recal Open Options (what version of Excel are you using?) and reset to Automatic. best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Danni" <Danni@discussions.microsoft.com> wrote in messa...

what's your question? If you're trying to get a count, use a third party product such as MessageStats (or use perfmon for basic counters). http://quest.com/messagestats D. "Marilyn" <anonymous@discussions.microsoft.com> wrote in message news:DF6A0DA4-BB2C-493D-B7A8-A96FAA583FFF@microsoft.com... > ...

Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should...

Hello, One of our business partners is having trouble w/an Excel document. They have the SUM function used multiple times throughout the document, and as they have changed values in certain fields, the SUM has automatically updated itself to reflect this. It's not working anymore. I have tried to redo the formulas but they simply won't work. The SUM stays the same regardless of the values in the cells. I can't even create a brand new column w/the SUM function at the bottom...it won't up date either. In a new document I can get the SUM function to work properly, how...

Our store has a number of different items that are tracked as kits... such as baked up meals, sandwiches, etc. Since there is no way to tell exactly how many sandwiches we can make to put in a starting number we have been starting at zero and going into a negative number for tracking purposes. Is there a way to set certain items in RMS to count up rather than down? Thanks for your help. ...

Lets say I have range A1:A5 containing values 1,2,3,4,5 respectively. I would like to count how many of these cells are odd and place that value in A6. The same for even numbers as well (in A7). Any help would be appreciated. -- yungexec ------------------------------------------------------------------------ yungexec's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22593 View this thread: http://www.excelforum.com/showthread.php?threadid=564950 Hi One way =SUMPRODUCT(--(MOD(A1:A5,2)=1)) For even numbers, change the 1 in the formula to 0 -- Regards Ro...

I have a query that I am using to show all records in a table, based on a couple criteria. One of the fields in the query [MEMBER ID] has data the repeats. I want to leave everything the way it is and add to this query a count of how many times a value in this field shows up in the table. I am able to do this if I only put [MEMBER ID] in a query twice, make it a totals query, and leave one on group by and put the other on count. When I try to just add [MEMBER ID] to my original query a second time and change one of them to count in the same way, the value for the count field will on...

I want to build a query with which to find the greatest number of figures in the field.I have a field called Plates where single numbers are entered.For example Visits Plates John 3 Jim 3 Deuce 3 Liza 3 Lenny 2 Lorry 6 Obvisouly in the above example we can count that the number 3 is the biggest number. How can i express that in a query ? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200708/1 SELECT Your_Table.Plates, Count(Your_Table.Plates) AS CountOfPlates FROM Your_Table GROU...

I would like to set up a worksheet in Excel for my check registor that will automatically give me the balance. jthhousing, have a look here and see if this will do what you want, http://office.microsoft.com/en-us/templates/TC010186511033.aspx If not I have one set up, if you would like to have a look at it let me know and I will send you a copy -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "jthhousing" <jthhousing@d...

Hi. I have a gradebook. I have 16 assignments, and thus have 16 columns for scores. However, I would like to include ONLY the highest 10 scores in the final grade. In other words, how can I find the sum of the largest 10 values in a 16-cell range? Thanks! =SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10")))) arary entered using CTRL+SHIFT+ENTER -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Best wishes to all, and hope f...

I'm not sure how to ask this, I am currently working on a spreadsheet that has an itemized worksheet depicting each budget approval (Sheet2). I have it tied into our complete budget so that it automatically adds and/or subtracts the amounts according to the date... =SUMIF('Sheet2'!$K:$K,"="&(TEXT(D$5,"mmm-yy")),'Sheet2'!$E:$E) ....but I also need it to be specific to the "BUD#" and "LINE#" (see below). SHEET2 A B C D E BUD # LINE # CAF Date Constructed Key Field Dollar Amount AUBUD 01 12/9/05 =TEXT(C1,"mmm-yy"...

I am trying to find the shortcut key(s) that lets me change the setting/functionality of the Auto Sum, Max, Min, etc. window in MS Excel. The window I am referring to is located in the bottom right hand corner of Excel, i.e. two places to the left of the NUM (displayed when Num Lock is on) window. Thank you for your help. James, I think you may be referring to the AutoCalculate feature which appears on the status bar when you have selected cells with values in them. By default it gives the sum of the cell values, but you can right click on the calculation in the status bar to change...

Hi all!! I have been trying to figure out how to NOT have excel count blank cells when it is counting my information that my formulas are telling it to count. This is my current formula (array): =SUMPRODUCT(--(oc!$C$1:$C$1000=435),--(oc!$H$1:$H$2000="approved"),--(oc!$D$1:$D$1000="telemedicine") I have to put a space in each blank cell (which could be a bunch) Thanks Dee -- imjustme ------------------------------------------------------------------------ imjustme's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26854 View this thread:...

Hi, I have a following data in a column on my spreadsheet 0.00 -98.35 #N/A -222.89 56.45 0.00 13.60 19.96 When trying to use the Sum function it doesn't work as it doesn' appear to like the #N/A. Any suggestion would be gratefully received. Thanks Simo -- sgrec ----------------------------------------------------------------------- sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1450 View this thread: http://www.excelforum.com/showthread.php?threadid=27181 sgrech wrote: > Hi, > > I have a following data in a column on my spreadshee...

Hi all Thanks for help in the past & future I have a columm of numbers to be added There are about 1200 of these running say from cells C1 down to C1200 I normally use the =SUM(C1:C1200) function but often I only want to total part of these - say from cells 250 to 650 I usually manually change the function each time - I am wondering if there is some function I could use where I could input say into cells B1 and B2 the numbers of the range of cells in the C columm I want to be totalled Many Thanks Alex Hi Alex, If cell A1 contains the row no to start from - say 6 and cell A2 has...

I am trying to calculate how many years an employee has worked. I have start date and current date. What is the formula to return years worked? If the current date is in A1 and the start date is in B1, then =year(A1)-year(B1) formatted as general will return the number of years worked. -- Gary''s Student "Paige" wrote: > I am trying to calculate how many years an employee has worked. I have start > date and current date. What is the formula to return years worked? Hi Paige One way =DATEDIF(Startdate,currentdate,"y") for years only =DATEDIF(Start...

I have the following Pivot table created: --------------------------------------------------------------------------- Sum of HEAD_COUNT DEPT PER_GROUP TYPE HQ HRD ED PD CFD --------- ---- -- --- -- -- --- ACCOUNTING 1APPROVED 0 0 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ACCOUNTING Total 0 0 0 0 0 0 ADMINISTRATIVE 1APPROVED 0 1 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ADMINISTRATIVE Total 0 1 0 0 0 0 CORPORATE SUP 1APPROVED 1 0 37 1 8 7 2ACTUAL 1 0 35 1 8 7 CORPORATE SUP Total 2 0 72 2 16 14 ----------------------...

OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

I know nearly nothing about Excel syntax, so please be kind. I have a list of people who are eating at a seminar. So far I hav some blank cells, some N for no, some Y for yes. I want to count the cells to get the number of people eating. I have =count(b5:b100,Y) which does not work. I have the format a general if that helps any. Please don't laugh at my syntax. :) Thanks for your help -- Message posted from http://www.ExcelForum.com Hi try =COUNTIF(B5:B100,"Y") -- Regards Frank Kabel Frankfurt, Germany > I know nearly nothing about Excel syntax, so please be ki...

I need a function that simply counts the number of cells in a range. Not based on a condtion, simply the number of cells in a range. Obviously if I'm doing this once I can simply put in a constant. (It's easy to see that the range A1:A50 has 50 cells.) But I need a formula that will automatically adjust if the size of the range changes, so that if I insert a row after A17, the formula will return 51. As far as I can tell, COUNT, COUNTA, and COUNTIF don't do this. I suppose I could use COUNTIF(A1:A50,"<>999"), where 999 is a value that I know will not app...

I have a spreadsheet in which I need to tabulate a total count of rows which meet a criteria. Example: Type Phase SubPhase "Occupied" 1 A I need a formula that counts the rows that meet a criteria such as Where phase is 1 and Subphase is A, count the number of rows that are "Occupied" Can anyone help - this seems like it should be easier. I may be missing something Thanks One way =SUMPRODUCT(--(A2:A100="Occupied"),--(B2:B100=1),--(C2:C100="A")) -- Regards, Peo Sjoblom "Dan Colgan" <anonymous@discussions.m...

Hi, I want to put a summary of Job orders in a table. (Excel (Zip) file attached) I am having a problem of total. I want to make a total of only approved job orders not the all job orders. Also if the column contains hot / cold type, I need red and bold for hot job order of complete row. Also I want to be counted by job type and entity. For example, in the total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how can i display in the abstract table. Can anyone help me please. +-------------------------------------------------------------------+ |Filename: count.zip ...

Hello. I searched for a solution to this, but couldn't quite find what I needed. I am importing data into five columns in Excel. Each of the cells is formatted as text, but most of them are blank. I want a count of the ones that are not blank. I have five columns for problem codes, so each row will have between zero and five problem codes. This is a little trickier than I thought because the problem codes can be either numbers or letters or a combination of both. How do I write a formula that says, "Look in these five cells and tell me how many of them have something meaningful...