Sum Last 5 Entries From Range

May 16, 2009

I have a row of golf scores "I3:X3" they either have a number value or "DNP".

I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.

ADVERTISEMENT

SUMIF Last (3) Entries In A Range Rather Than Entire Range

Sep 14, 2013

I'm looking to build a football data sheet on matches played with a column that adds up the last 3 match goals for the team in that row. I've only managed to get as far as adding up all goals for a team in a static date range using SUMIF. I imagine this is more complex and requires some kind of changing sum range that's relative to the cell I'm in.

I'm intermediate at best so any array formulas and VB script would preferred to be avoided if possible.

in the watered down example below, I'd like to add up all home team goals for Hereford in the last 3 matches, therefore excluding the game on the 10th (row 1) giving a total of 5. I'd like this formula to copy down from a13 to future matches and therefore update. So the next week's match will add goals for hereford from e13,e11,e8 but exclude e2, e5. Yes you've guessed it... I'm trying to work out recent team form to predict match outcomes for financial gain...

View 4 Replies View Related

VBA Counting Used Entries In A Range

Jul 22, 2009

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..

View 4 Replies View Related

Extract Last And Next-to-last Entries In A Range

Sep 6, 2005

Assuming that the entries are text values:

For the next to the last entry:

=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)

For the last entry:

=LOOKUP(REPT("Z",255),A:A) .....

View 14 Replies View Related

Counting Entries In Date Range?

Feb 12, 2014

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

View 6 Replies View Related

COUNTIF (count How Many Entries There Are Within A Range)

Nov 12, 2008

I have a column of values in "£"s and i need to count how many entries there are within a range. I have been able to do it with text but not money values with £ signs!!

View 2 Replies View Related

Finding Duplicate Entries In A Range?

Jun 20, 2013

I have a spreadsheet I use for creating golf score cards. I select the golfers from a drop down list and display a message if a golfer has already been selected. The ranges I want to use are A7:A15, A25:A33, A43:A51, A60:A68. My code is below and I am getting an error saying 'compile error, argument not optional' and UNION is highlighted.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union(Range("A7:A15, A25:A33, A43:A51, A60:A68"))) Is Nothing Then
With WorksheetFunction

[Code]....

View 2 Replies View Related

Count Unique Entries In A Range

Sep 5, 2007

count unique entries in the Range A1:A10

i have data which repeats but i ant to count only unique entries?

View 9 Replies View Related

Message Box Displaying Some Entries In A Range

Apr 30, 2009

I have a range (A5:DG5). Some of these cells are empty, some are populated.

I would like to display a vbYesNo box that shows the value of each populated cell on a different line, with no gaps for non-populated cells.

View 9 Replies View Related

Prevent Duplicate Entries In Range

Dec 30, 2009

Option Explicit
Dim myCount As Byte
Dim myColumn As String
Dim myRow As Long
Sub Worksheet_Change(ByVal Target As Range)
myCount = 0
If Range("B" & myRow).Value <> "" Then myCount = myCount + 1
If Range("C" & myRow).Value <> "" Then myCount = myCount + 1
If Range("D" & myRow).Value <> "" Then myCount = myCount + 1
If Range("E" & myRow).Value <> "" Then myCount = myCount + 1
If myCount > 1 Then....................

The above code pops up the message box when a second entry is made within the specified range, but OK button does not function, contents are not cleared, and Excel locks up.

View 2 Replies View Related

Delete Entries In A Range That Equal 0.0000%

Mar 16, 2009

I have a very big range of data from B4, to a variable other end from which I would like to delete all entries equal to 0.0000 leaving just those with an entered value.

I guess it's just an if question cycling through the rows and columns? Slight complication is it's on the 3rd sheet of a Workbook, as set out in the sample file.

After this manipulation has been done, I then wish to copy the data from the range B4: end of data into the same cells in the output sheet.

View 7 Replies View Related

Move Or Remove Negative Entries In A Range

Dec 16, 2008

My check register was exported to a excel spread sheet where the deposits and debits are all in one column E. The debits are entered as a negative number. I want to create two separate debit and deposit columns with the appropriate data entered. What the best way of moving the data accordingly?

View 3 Replies View Related

Limiting Number Of Entries Allowed In A Range?

Aug 15, 2013

Im building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.

I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)

The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)

My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.

View 2 Replies View Related

Loop For Removing Double Entries In A Range

Apr 24, 2007

I´working on an excel sheet where i´m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)

Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................

The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn´t have any effect.

View 5 Replies View Related

Using Two Cell Entries To Call Range For Function Evaluation?

Sep 7, 2013

In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.

I have a data panel in B1:D20.

I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.

Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.

View 5 Replies View Related

Count Unique Entries Within Variable Date Range ..

Apr 27, 2009

I've been struggling for hours on what should be a simple formula. I have 6 columns containing various dates. On each row I want to count of the 6 columns how many dates were unique and after 3/15/09. I've been using the following formula however it still counts a cell even if it's prior to 3/15/09. =SUM(IF(FREQUENCY(A1:F1,A1:F1)>3/15/2009,1,0)). I've attached a sample file for reference.

View 2 Replies View Related

Set Maximum Number Of Entries In A Range - Data Validation?

Dec 8, 2012

Basically in cells b12:b26 (15 cells) I need to enter the type of item (which is limited to 2 items). I made a drop down list for that range which works perfectly, therefore the user can choose either item 1 or item 2 or blank. However, I need to figure out a way so that item 1 can be chosen a maximum of 10 times in the range, and item 2 can only be chosen 5 times in that same range..

I am assuming that you can't add that condition to the drop down list... So I was thinking of adding an extra column duplicating b12:b26 (c12:=b12 etc...)- and entering a data validation there.

I was going to enter the following:

if item 1 is chose : =NOT(COUNT($c$12:$c$26)>10) &
if item 2 is chosen: =NOT(COUNT($c$6:$c$6)>5).

However, for some reason it doesn't work - it doesn't block the entry if I try to enter 11 item 1s or 6 item 2s.

View 2 Replies View Related

Validate Cell Entries To Named Range List

Apr 28, 2009

I have been trying to figure out how to use a named range (on another sheet) to validate the user entered data in a specific column. All my attempts at utilizing worksheet_change event have ended in errors. I've created a simple workbook that shows what I'm trying to do and have attached it to this message. The 'Sample (Data)' worksheet has the data table that would be completed by the user. On the 'Validation' worksheet I have named the range to be used to validate the data as 'rngVal'. So when something is entered under the 'Expense Type' heading, the code would verify that the value entered was contained in the 'myVal' validation list. If it was not present in the list, a msgbox would instruct you to try again. After stealing bits of knowledge and code from many of the posts on this site I cobbled together the following (which results in an error at the 'set rngFind' line):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range
'Set Target = Cells(ActiveCell.Row, ActiveCell.Column)
Set rngFind = Range("rngVal"). Find(Target.Value)
'If data in column C changes, do the following
If Target.Column = 3 Then
'If the value is found on the validation list, do nothing.
If rngFind Is Nothing Then
Else
MsgBox "You must enter one of the following in this cell:"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If
Else
End If
End Sub

View 6 Replies View Related

Count Unique Entries Within Variable Date Range

Jan 26, 2008

Using the DCOUNT function is generally a straight forward proposition but I'm not getting the expected results and would like for someone to take a look and help me understand why.

Goal: create a count of unique entries within a defined variable date range

I have a data table with duplicate values and need to count unique entries, the result of which will be used in a calculation. Due to a requirement to track the counts in a rolling 30-day period, the flexibility of daily selecting the date ranges is a necessity, which is why I chose to use DCOUNT and feed dates into the criteria cells.

I've been attempting to use the DCOUNT function but I'm not getting the correct result.
Oddly, after duplicating the table and formula on the "Count Repeated Items Once" page, even those results are incorrect.

It seems, too, that COUNTIF does not like (accept) dynamic named ranges. Hard coding the range into the formula yields a result of TRUE, but using a dynamic named range gives FALSE. Anyone else experience this and is there a work around (that is, if I have not erred in its use)?

View 9 Replies View Related

Count Unique Text Entries Given A Certain Condition In A Separate Range?

Aug 11, 2014

I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.

Company Name
Company Type

Company A
SME

Company B
SME

(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.

View 5 Replies View Related

Count Highest Number Of Duplicate Text Entries In Range

Nov 2, 2011

I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.

I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.

If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.

View 6 Replies View Related

Duplicates Finding Entries In Column A Which Match Any Of Column C Entries

May 7, 2014

What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.

Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).

the function I'm working with right now is

=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)

View 13 Replies View Related

Identifying Unique Entries Based On Unique Entries In Another Column?

May 29, 2014

I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.

Example List

Fruit
Color
Apple

[Code]....

View 9 Replies View Related

Counting Date Entries Between A Date Range

May 7, 2009

I 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 Related

Sum Only The Last Four Entries

Jan 21, 2009

I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.

View 5 Replies View Related

Sum Only Same Entries

Jul 29, 2006

I would like help with a worksheet.

I have a column of Data which is a "material description".

In an adjacent column I have the square meterage of the material.

I would like to total all the square meterage of each material and have it shown in another column.

How can I do this?

I have searched far and wide for an example however I am probably using the wrong terms in my search.

Attached is a sample workbook.

View 9 Replies View Related

Number Of Entries Above Certain Value

Aug 11, 2014

To give this a bit of context, I am looking at running speed of an athlete. I am trying to determine the NUMBER of sprints in a session (aka >15 km/hr for example). To do this, I need to calculate the number of times a speed vs time graph surpasses 15km/h.

For example, using this data:

TimeVelocity
0.212
0.413
0.614
0.815
116
1.217
1.416
1.615
1.814
214
2.212
2.410
2.613
2.814
315
3.216
3.417
3.618
3.817
415
4.214
4.413
4.612
4.812
511

I would calculate two sprints (one beginning at time point 0.8, one beginning at time point 3). I can't work out how to calculate these sprints independently, rather than counting the total number of entries above 15km/hr (11 in this example).

View 3 Replies View Related

Formula To Add The Entries

Jun 12, 2009

If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)

=COUNTA(D6:D15)

TS
TS
VR
IS
IS
OS
VR
OS
8 (total)

View 7 Replies View Related

Remove Entries In Col A That Appear In Col C

Jun 10, 2009

Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.

Hope this all makes sense. Please feel free to ask if it is not clear.

View 14 Replies View Related

Offsetting Entries

Oct 28, 2009

I m writing the numbers 1 to 100 in rows of 10. I now need to change the code to offset this, starting at any cell i choose.

if i was to type e.g D6 into cell A1, the numbers would start at D6.

View 3 Replies View Related

Flagging Same Entries

Nov 23, 2006

If I have a multiple entry of a product with the same end date against a customer number how could I flag this as something I need to look at?

Eg
Cust number Product Number Expired Date
1000018000 31/12/2006
1000018000 31/12/2006
10000180002811/2006
1000018001 31/12/2006
1001020010 31/12/2006
1021518000 31/12/2006

So in this case the first two would be flag with say “yes” and the others “no” as the customer number, product number and expire date don’t match.

View 9 Replies View Related

Conditional Entries

Jul 18, 2007

I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.

I'd like to evaluate these last 8 entries using a couple of different conditions.

#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...

a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)

#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and 0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)

b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)

View 9 Replies View Related

How Many Different Entries For A Certain Criteria

Aug 26, 2008

I have been searching a bit in a lot of the already posted threads but without any luck on my problem.

Normaly in excel you always want to count how many entries for a certain criteria.

Now I just want to know how many different entries for a certain criteria.

Example:

in column A1:A10 I have the value "S" -> group

in column B1:B4 I have the value "40" -> material number
in column B5:B8 I have the value "60" -> material number
in column B9:B10 I have the value "70" -> material number
....
....
etc.

So for the group "S" it is listet with total 3 different "material number" (40,60,70)

So my result should be = 3

how should the formula be on counting for the group "S" how many different values are in a defined area (e.g. B1:B10)

I have tried with sumproduct,

View 9 Replies View Related

Averages For Last 12 Entries

Oct 20, 2008

I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted.

The solution needs to work on percentages as well.

To explain further
Col A Col B
Jan 2007 1
Feb 2007 5
Mar 2007 5
Apr 2007 0
May 2007
Jun 2007 20
Jul 2007 0
Aug 2007
Sep 2007 10
Oct 2007 0
Nov 2007 10
Dec 2007 18
Jan 2008 8
Feb 2008 6
Mar 2008 0
Apr 2008
May 2008 10
Jun 2008 1

The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.

View 9 Replies View Related

Sum First 13 Entries Only (formula)

Oct 30, 2008

Is it possible to have a formula that sums only the first 13 entries in the range f8:f1006, where the value 13 can change as it is a result of another formula linked in cell f6

View 9 Replies View Related

Vba Sum Up Textbox Entries

Feb 25, 2009

I try to sum up values a user enters into 3 different textboxes using the following
cells(1,1) = userform.textbox1.value + userform.textbox2.value + userform.textbox3.value
Let's imagine the user enters "10" into each of the three textboxes, cell a1 should contain 30, however, what i get is 101010.

View 9 Replies View Related

Display Last 8 Entries Only

Sep 19, 2009

I have data on sheet A columns (A:DR) over 400 rows, adding more all the time.

On sheet 2 i would like to display only the last 8 entries (rows) from sheet 1 columns A,B,DO,DQ

So i will end up with 8 rows of data on sheet 2, and 4 columns.

View 9 Replies View Related

Duplicate Entries

Sep 24, 2009

I have been trying a number of different functions!

I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.

Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?

=(COUNTIF('Cases Closed'!O:O,"John"))-(COUNTIFS('Cases Closed'!O:O, "John", 'Cases Closed'!X:X, "*Solution*"))

View 9 Replies View Related

Restricted Entries

Nov 30, 2009

I want to put a formula in a sheet.

Suppose In issue column there are few nos but until those nos will not be posted in Return column, those will not be posted again in another day's issue column, similarly until those will not be returned, those nos will not be posted in issue column. But for the first two days it will not work ad those are first time entries.

But this will be continued working on the next sheet for the net month.

28/11/09 29/11/09 30/11/09 Issue Nos. Return Nos. Issue Nos. Return Nos. Issue Nos. Return Nos. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

View 9 Replies View Related

Deleting Old Entries

Dec 16, 2009

The following code works fine if the data is grouped (not necessarily sorted) by column B (but not necessarily by column A), this code will delete the duplicates rows, but retaining the latest entry (by column A) of each name in column B.

Sub DeleteTheOldies()
Dim RowNdx As Long
For RowNdx = Range("B2").End(xlDown).Row To 2 Step -1
If Cells(RowNdx, "B").Value = Cells(RowNdx - 1, "B").Value Then
If Cells(RowNdx, "A").Value

View 9 Replies View Related

Restrict Entries To 1 Per Row

Nov 16, 2006

I am looking for a VBA event(?) code that would erase another cell if I enter a value in another cell, because the criteria is that only one of the cells are to be allowed a value per row.

ex: cells A1, B1, C1
if I enter 1 in cell A1 and then enter 1 in cell C1, I want cell A1 to erase and cell C1 would have the value 1. I am looking to create these sets for 200 rows.

View 3 Replies View Related

ADVERTISEMENT