I'm trying to create a worksheet with only one unprotected cell (simple enough!) and I want to base calculations in other protected cells on a running total of how many times a value has been entered in my unprotected cell. Re-entering the same value would need to be counted.
First enhancement would then be a means of resetting the counter.
Second enhancement would be to check for a valid entry and only then increment the counter.
Colum A
Ticket No.
150258, 150268, 150269
150259
150249, 150266, 150271, 150279
Colum B
No. of Stillages
3
1
4
I have two colums. In A1:A4 are ticket numbers of products made. In B1:B4 are the number of stillages that the tickets relate to. What i am trying to work out, is a formula that will calculate the number of stillages produced based on the fact that each ticket number begins with "150". I have tried using COUNTIF, MATCH and FIND functions, but when i does work, will only return a value of 1.
Couting Unique Entries
I've included a sample worksheet ...
i need to count the number of used entries in a range.
so fex the range is A1:A10 but there are only 2 entries what i search is the number 2
is there a VBA function or so doing that.
ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..
Column A contains names
Column B their unique ID number
Column C the date a transaction occurred
How can I count the number of entries between two dates i.e. 1-31 April regardless of duplication or anything? I've tried a few things mentioned in other threads but none seem to work.
My spreadsheet contains two text columns that represent dates and times; 11/21/2007 and 0935 for example. The rows are populated with the date and time for every event during a 48 hour period. A single worksheet may contain up to 3,000 events (rows).
How do I create a subtotal for the number of events in each of the forty-eight 1-hour periods?
I am trying to determine wether there are duplicate entries within a range and have then counted also. Example:
I have a list if zip codes and I want to find out how many of each zip code are there. If I have a list with:
12345
12345
12345
12346
12346
12347
I want to have it tell me there are 3 of "12345" and 2 of "12346", possibly next to the entry?
12345 3
12345
12345
12346 2
12346
12347
I know that there is a lot on locking cells but it is confusing to me. I am able to lock a worksheet but that is not what I need. I have an Excel document that has thirty to thiry-six names on it. I do not want anyone able to edit the names. But I do want them to be able to add a name.
Example:
There are 30 students in the classroom. A new student comes in. I want the teacher to be able to add that student to the roster but not able to edit the names above. What do I need to do?
I'm looking for a way to count entries of numbers per day of the week within a date range. Example
Start End Mo Tu We Th Fr Sa Su TOTAL
2/10 2/23 1 1 1 1 0 1 1 12
3/1 3/31 1 2 1 1 1 1 2 40
I don't know if that possible in excel or its to much
I need to count the number of unique names (column A) on 3 separate worksheets. Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.
I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names. To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))
I have a column of dates and want to count how many times there is an entry for a particular month. example:
(column D)
7/4/2009
7/13/2009
7/22/2009
8/1/2009
8/6/2009
9/15/2009
I want to be able to coun (countif?) how many times there is a July listing, August listing, etc.
=COUNTIF(D3:D20,">=7/1/2009")+COUNTIF(D3:D20,"<=7/31/2009")
=SUMPRODUCT(--(D3:D20>="7/1/2009")*(D3:D20<="7/31/2009"))
I have made them arrays.
Im trying to make an excel spreadsheet for work for working out how many of each product and individual staff member has sold
I have 2 columns one with staff and one with what they sold e.g.
Staff Member - Sales
Staff 1 - prod A
Staff 2 - prod B
Staff 3 - prod A
Staff 2 - prod C
Staff 1 - prod A
and have another table, the one that i want to populate, that looks like this
Staff 1
prod A =
prod B =
prod C =
Staff 2
prod A =
prod B =
prod C =
etc
I cant seem to find a function that will work out how many of prod A staff 1 has sold and how many prod B he has sold etc
i have tried things like countif but i cant seem to get them to link the staff member and sales boxes together, the only way i can think of is to write down an equation for each line, but i have hundreds of sales and that would takes ages
I run an online golf tour and I need a little help with the coding of my handicap excel sheet. (For any golfers out there this is a custom handicap system, not the R&A version).
First I'll explain what I have, then my problem.
I have from left to right the following, Name, Scores, Worst Score, 2nd Worst Score, Average, Hcp. (There are other columns but are not important here)
The Scores columns record each round played, so at the moment we only have 10 columns with data as its the start of the season. I currently find the "Worst Scores" from the 10 columns, as they are all relevant at the moment. My problem will arise when I get over 12 columns of data. From that point on I need to find the "HIGHEST 10" from the "LAST 12" entries.
Easy you might say, BUT the last 12 rounds played could be spread over 100+ columns and every row could be different.
Example, Player 1 could have scores in column A to AB and then in AD to AE. Where as Player 2 could have scores in every alternate column.
I'm assuming I'll have to set up some form of count to count the cells that have data in them until I get to 12. Then extract the ones I dont need.
My question is, How do I find the last 12 column cells used in any row AND extract the highest 2 from those 12?
I need to generate a report that indicates how long it took a vendor to respond, and indicate if the time was less than 2 hours.
I'm having trouble with a formula that will indicate how long it took for the vendor to respond during business hours (8-5 monday through friday). I can easily calculate the total elapsed time, but I'm at a loss on how to account for the after hours time period.
Would this be better handled with VBA?
I need to count entries in a validation field
I tried using =COUNT(3,E3,M3,U3,AC3) but this does not work.
I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.
View 9 Replies View RelatedI have two worksheets, 'monthly budget' and 'summary sheet'.
Summary sheet has been set up so that any additional records added to 'Monthly Budget' will be copied using the formula below (kindly provided by Cheeky Charlie yesterday):-
=IF(ISBLANK(INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A))),"",INDEX('Monthly Budget'!$A:$G,ROWS($1:1),COLUMNS($A:A)))
I have set up a unique entry count on 'summary sheet' which is currently showing 5. If I add a new name 'Jim' to the 'Monthly budget' sheet, this is copied on the 'summary sheet' however the unique entry count remains at 5.
Every other line is the planned production and the others are filled in after as the actual production
I have a sumproduct formula right now that looks if the line is considered planned production and then if there is a number greater than 0 planned on that day. The idea is to tell me the number of Stations scheduled to run that day.
Here is my problem- if a station is running two different products it gets counted twice.
I am trying to find a way that once a station is counted it only gets counted once even though they are on seperate lines.
Attached is an example of what I have with the formula and below that is what I am looking for
How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);
Calculate the sum of the numbers in column D that occur on the 10th January 2014?
And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.
Counting Unique text entries in a sheet with a condition ..
View 14 Replies View RelatedI have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
View 3 Replies View RelatedI have 2 columns that I need to use simultaneously to conclude something.
Column A
Row 1: Dog
Row 2: Cat
Row 3: Bird
Row 4: Dog
Row 5: Dog
Row 6: Dog
Column B
Row 1: See Vet
Row 2: See Vet
Row 3: Get Rest
Row 4: Get Rest
Row 5: Get Rest
Row 6: See Vet
Okay, so I have these columns, and I want to count the unique number of animals that need to see a vet, or get rest. Based on the table above, there are 2 unique animals that should see the vet (Dog and Cat), and 2 unique animals that should get rest (Dog and Bird). how to do this in excel.
I'm trying to count the number of non-empty cells beneath a reference cell and would be most grateful for a few pointers. The code I currently have (that doesn't work) is:
View 2 Replies View RelatedHave you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies View RelatedI am trying to insert a formula that counts the number of dates within a certain date range. why this sample file does not work, the answer I am looking for in cell C1 is 3.
View 2 Replies View RelatedI've created a spreadsheet that I want to offer for sale.
I've protected all cells with formula and those not needed to use the program. The only cells that can be selected/changed are those that need the values for the calculations.
How do I protect the sheet from being saved/stolen when trialed by possible customers?
I made a sheet to control some reports, and it works like this: you click a button ("Insert"), it opens a userform, you type everything there (date, serial number, client, city) and it place that info on the first row available. Then there's a "Clear Last Entry" button, and that's it.
I would like to protect the whole sheet and only allow users to insert new entries via the "Insert" button. How can I do this? If I protect normally Excel won't allow the VBA code to write on cells.
The Special GoTo function doesn't have a listing for highlighting (going to) only protected or (preferribly) unprotected cells. Is there an easy way to do this?
View 9 Replies View RelatedI have a macro that turns off filtering option when it runs. What I want is for to first warn the user that rows have been filtered and the Macro will not run, then once the filters are switch off, the macro will run. It does the last bit at the moment but switches off the filtering of protected cells and once the macro has run the sheet can no longer be filtered .
Sub NewRowSQT()
Dim ans As String
ans = MsgBox("Ensure you have selected a cell in column B immediately below the section you wish to add to or the Macro will FAIL!", vbYesNo)
[Code]....
Excel 2007 or 2003 Is there code to be able choose the formating of a cell when right clicked if the cell is Unprotected. But, the sheet is Protect and the Workbook is Protected. Without Unprotecting anything 1st.
The reason being is sometimes the user may need to change the type of font from Arial, or Gdt or Y14.5M.
