# Excel 2010 :: Sum (x) Previous Occurrences?

Sep 20, 2012
Excel 2007/10

The formula very nearly does what I need but I don't understand it well enough to adapt it.

Count the last three occurrences

My sheet looks like this;

A

B

C

D

E

F

G

[code].....

I modified this;

=IF(COUNTIF(B$2:B2,B2)

View 5 Replies
ADVERTISEMENT
Nov 9, 2012

I have a spreadsheet with 6 months of data in one row. the dates are from A3 (Jan 1) to FY3 (Jun 30). The data is in range A4:FY4. The data is a "$" (without quotes). The $'s are entered randomly during the 6 month period. I need a formula to determine the days between the last occurrence and the next to last occurrence of the $. Example spreadsheet shows the $'s on Mar 28 and May 5 as the last two. The spreadsheet is attached. I am using Microsoft Office Excel 2010.

View 6 Replies
View Related
Mar 31, 2014

I have data arranged in a worksheet (see attachment) that has hours of work broken down by day. What I need is a formula that will find the number of times a record occurred in Column F that is greater than or equal to 12 hours each day. So for March 1st there would be 9 times. I can do that now with no problem using "=COUNTIF(F4:F14,">=12")" However, the real thing that I need is how many days of each month were there only 1 count (of 12 hours or more). So it needs to look at the range of data that goes from 3/1/13 to 3/31/13 and find the total number of days that had 1 count (of 12 hours or more) each day and return the number of days it found.

View 6 Replies
View Related
May 28, 2014

I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.

I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".

For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.

The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.

There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.

View 7 Replies
View Related
Mar 27, 2014

formula to take a value from one sheet and add to it in Excel 2010? I'm naming the sheets but not necessarily numerically. I have done it by manually entering the sheet name but would like it to figure out the sheet name automatically based on where the sheet is located in the workbook. That way I can copy the current sheet, rename it and still have it update properly with a running total. Here is what I have now: =D7+'011514'!E7

View 2 Replies
View Related
Apr 22, 2014

In a datafile I have one column containing a trip 'origin' and a second column contains 'destinations'. I want to count how many times each trip occurs (so the same origin/destination pair). This is doable using COUNTIFS but unfortunately respondents did not provide consequent origins and destinations. I encountered the following formulations

-Alfastraat, Amsterdam

-Alfastraat 5, Amsterdam

-Alfastraat 5, 1021AB Amsterdam

-Amsterdam, Alfastraat

Since the format is different among and within respondents functions containing LEFT, RIGHT, MID are not useful (at least, my trials did fail). I found a VBA-script for a FUZZYVLOOKUP which sounded promising. Unfortunately the data is stored on a remote PC without VBA on it.

Is there a way to count the occurrences of trips given the circumstances?

View 1 Replies
View Related
Aug 15, 2014

I am working on a big project involving baseball statistics, evaluating individual seasons. After a great deal of entertaining data gathering, I have a spreadsheet of 1896 seasons that meet certain criteria--that is, seasons belong to an individual player. The player's names are in column A of my spreadsheet.

Individual names appear between 1 and 15 times. I would very much like to generate a quick table (or just a couple of columns) that lists every name in column A, and how many times it appears. My understanding is that the histogram function won't work with text. I'm running Excel 2013 on windows.

View 7 Replies
View Related
Jan 27, 2014

I am using Microsoft 2013 and am looking for 4 formulas in order to split 1 cell in to 4 (across same row). Number of characters varies between each instance of """.

I would like the formulas to start in column B-D (data in A)

I am looking at formulas based on specific instances of a quote mark in cell from data in column A

Formula 1 - Return with characters up to and including the 5th instance of """

Formula 2 - Return with characters after 5th """ and up to and including 6th """

Formula 3 - Return with characters after 6th """ and up to and including 7th """

Formula 4 - Return with characters after the 7th instance of """

Example below...

Cell A1

xxyy","aabb","ee,ff,""gghh"hh,"llmm,mmnbijp"oossww"

Desired results

Cell B1 - Formula 1

xxyy","aabb","ee,ff,"

Cell C1 - Formula 2

"gghh"

Cell D1 - Formula 3

hh,"

Cell E1 - Formula 4

mmnbijp"oossww"

View 6 Replies
View Related
May 25, 2014

I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.

Col A

ABCD

ABCD

ABCE

BDCE

BDCF

ZXYE

ZXYE

YEZY

CDEF

Col B is where i would like to list all the unquie vaules with a count e.g.

ABCD 2

ABCE 1

BDCE 1

BDCF 1

ZXYE 2

YEZY 1

CDEF 1

View 14 Replies
View Related
Jan 29, 2014

I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.

formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?

View 2 Replies
View Related
Nov 13, 2013

I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.

I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.

View 2 Replies
View Related
Dec 10, 2012

see attached file. Need to find latest non blank value - in attached file it is highlighted in yellow. From there, want to summarise 26 weeks back so, in the attached file:

Row 2 would be finding 750 and summarised back 26 weeks from 30 sep 2012

Row 3 would be finding 2250 and summarised back 26 weeks from 2 dec 2012

Row 4 would be finding 5000 and summarised back 26 weeks from 4 nov 2012

View 7 Replies
View Related
Mar 25, 2014

I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.

View 6 Replies
View Related
May 20, 2014

I ma using exel 2007 and I tryed to use this codes,I need faster to finish this project

Soo I have done only 2 buttons OK and Close the tab works like this I CLICK "Kerko" and then I write the name that I want to search in my Phonelist and when I click Ok it shows me his surname his telephone name and his City prephix

But the next and previous button I cant make them work,soo when I have to click next the name down the name that I first wroted will apear with his surname telepphone name and city prphix,and when I click prevoious will happend the same think but will not apear the next persone but the previous.

View 1 Replies
View Related
Mar 26, 2012

Excel 2003. I have a list of names in column A, dates in column B, I need the difference between dates for the people in column A, I am doing it now with the formula shown but I need a formula to put in column C that will look at the name in column A, for that row, and then find that name above it and give me the difference between the to dates in column B. So in the example below if I put the formula in C7 it would look at A6-A2 for harry and then give me the difference between B7 and B4, 6 days, if there is not a match return blank.

Excel 2003ABCD1NamesDateDays Between2tom2/8/123****3/2/124harry3/9/125tom3/10/1231=B5-B26tom3/12/122=B6-B57harry3/15/126=B7-B48****3/26/1224=B8-B3Sheet1

View 4 Replies
View Related
Feb 19, 2013

I'm creating an excel document that tracks the amount of time someone has (in months) in the program. Certain residents are able to 'fast track' if they meet conditions, and I am trying to create this spreadsheet so that anyone who looks at it can tell who qualifies (and when).

The issue I'm having is with conditional formatting, because I don't know how to do it with mutliple conditions.

Column C is their previous time (months) in the program

Column D is their current time (months) in the program

Column E is their total time in the program (Sum C+D)

Coumn E is what I would like to format, based on the following rules set out in the program manual:

If previous time is 9 months or more, a resident is eligible to fast track after 6 months current time (format green).

If previous time is less than 6 months, a resident is eligible to fast track after 9 months current time (format green).

I understand that their is an odd gap - but these are the rules currently set out by the program manual, which is what I have to follow.

I'm using Excel 2007 - if that changes anything.

View 3 Replies
View Related
May 10, 2014

Windows 7, Excel 2007. a formula to find the next lowest sequential number within a group.

Here's an example data set, which represents transactions by date and category:

Col A is a user entered Date which will not be in chronological order (unfortunately a sort is not an option)Col B is a user entered CategoryCol C is a formula that gives the chronological sort order based on the date.

The formula in C2:C9 is =COUNT($A$2:$A$9)-(RANK(A2,$A$2:$A$9)+COUNTIF($A$2:A2,A2)-1)+1Col D is a formula to determine if the row is the first chronological transaction within the Category

The array formula in D2:D9 is =IF(MIN(IF($B$2:$B$9=B2,$C$2:$C$9))=C2,"Y","")

This might not be a necessary column, but can be used as a helperCol E is where I would like a formula to give the next lowest value in Col C for the Category in Col B (the order number in Col C for the previous transaction within the same category).

I have entered in the values that the formula should return in Col E in the table below

A

B

C

D

E

[Code]...

If the current line is the first transaction of the category, the formula should return the value in Col C for that line (so E2 would equal the value in C2, which is 2).

So the first part of the formula could be straightforward, using the helper column:

=IF(D2="Y",C2,

Here's the tricky part: If it's not the first occurrence of the category, then it should return the value of Col C for previous transaction (or the next lowest number in Col C that is within the same category).

E3 would return 4, since the previous transaction for Category A was line 9

C3 = 6, and of all the values of Col C for Category A (2, 4, 6, 8), 4 is the next lowestE4 would return 6, since the previous transaction for Category A was line 3 C4 = 8, and of all the values of Col C for Category A (2, 4, 6, 8), 6 is the next lowestE5 would return 1, since it is the first transaction in Category BE6 would return 1, since the previous transaction for Category B was line 5 C6 = 5, and of all the values of Col C for Catebory B (1, 5, 7), 1 is the next lowestEtc.

I'm thinking I need an array formula to find the minimum value within the category, where that value is less than the value of Col C in the current line. Just not sure how to write that...

View 9 Replies
View Related
Jan 20, 2014

I need a formula to automatically change the summary column according to the month we are in.

Ie:

Last Months Data

Nov

Dec

Jan

Feb

1

8

1

7

4

7

9

2

9

'Last Months Data' column should show Dec. However, as we move into February and I complete the 'Jan' column, I would like 'Last Months Data' to automatically change to show Jan's data - is this possible?

I was previously told to use the following formula but this would automatically select the current months data and not the previous months data which is what i need - =INDEX(B2:L2,MONTH(TODAY()))

View 2 Replies
View Related
Mar 5, 2012

I have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)

Sub aaa()

'

' aaa Macro

'

[Code].....

View 1 Replies
View Related
Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()

Dim n

Dim V

Range("A1").Select

V = ActiveCell.Value

[Code] ........

View 1 Replies
View Related
Apr 1, 2014

I am trying to adjust the below macro so that it will work in Excel 2010.

Sub OpenAndProcess()

Dim fs As FileSearch

Dim I As Integer

[Code]....

View 3 Replies
View Related
May 1, 2014

keep getting "The formula you typed contains an error".

To calculate a person (or anything else) age you basically subtract his date of birth from today's date. However which date functions to use depends on the amount of precision and the output format you want.

3. If we want his age in years and months, then we can use a formula like this one:

=INT((TODAY()-A1)/365.25) & ” years and ” & INT(MOD((TODAY()-A1)/365.25;1)*12) & ” months”

The output of this formula will be as follows:

40 years and 2 months.

I change A1 to G3 because that is where I the birth date.

View 2 Replies
View Related
Jul 8, 2014

I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.

The layout is similar to this :

H1, H2, H3, H4, H5, H6

A4, A3, dummy, Dummy, ~,

A4, A3, dummy, "Dummy ,Dummy", ,

A4, A3, dummy, Dummy, +,

A4, A3, dummy, Dummy, -,

A4, A3, dummy, Dummy, "(5 + 4) /4",

Consider that the formula (5 + 4) /4 has a CR and LF between (5+4) and /4

I've tried a few methods to import the files:

Reading the CSV file as a Text File a line at a time but with the CR a LF it get's all messed up

[Code] .....

Tried with Using OLEDB but the Sign + , - and ~ are read a Null

[Code] ....

And Finally tried wit th QueryTable but it does not recognize the Double Quote that precedes the comma so

"Dummy ,Dummy" it's seen as two column values instead of one.

[Code] .....

View 4 Replies
View Related
Nov 12, 2011

I am trying to use the code that can find the last used row in particular column.

Here is the code in module1

Code:

Function LastRow(rng As Range)

Dim temp, temp1

Dim col As Range

With Application.Caller.Parent

For Each col In rng.Columns

temp = Cells(Rows.Count, rng.Column).End(xlUp).Row

If temp > temp1 Then temp1 = temp

Next col

End With

LastRow = temp1

End Function

and code in the range

Code:

=LastRow(V12:V300)

My problem is, this code was working nicely but now it only shows ###.

PS I use MS office 2010

View 9 Replies
View Related
Feb 16, 2012

I have just upgraded Excel from 2003 to 2010 and cannot locate where I would convert a spreadsheet to a .pdf document.

View 2 Replies
View Related
Jul 11, 2012

I have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words.

A1 = word 1

A2 = def. 1

A3 = word 2

A4 = def. 2

A5 = word 3

A6 = def. 3

Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming.

I'm using Excel 2010.

View 4 Replies
View Related
Aug 26, 2012

Is there functionality in Excel 2010 to convert a cell that has last name, first name to first name last name? For instance:

Before: Mouse, MickeyAfter: Mickey Mouse

I'm trying to merge data from 2 sources, and the common data between the two is the name. However, it is formatted differently in each data source.

View 2 Replies
View Related
Oct 15, 2012

USING EXCEL 2010

I am looking for a formula that will provide the right most value in a row.

View 9 Replies
View Related
Nov 28, 2012

I need to get the row number of the next available row , and my column contains many blanks .

Code:

Selection.End(xlDown).Select

Selection.End(xlUp).Select

will not work, nor will

Code:

Blast = WorksheetFunction.CountA(Range("b:b"))

HTML Code:

Excel 2010ABCDE230/09/2012SC12274-1brine 30/9/1231/10/2012FR12275-1brine 1/10/1241/10/2012BQ12275-

1yc12275-159/10/2012FR12283-1brine 9/10/1269/10/2012SC12283-1yc12283-1710/10/2012BQ12284-

1brine 10/10/12815/10/2012brine 15/10/12916/10/2012FR12290-11016/10/2012BQ12290-11122/10/2012SC12296-

[Code]....

In column b the next available row would be 16 ( the first 30/10/2012 row ), if I use the counta system i would get 7, and the xldown / xlup system would need to depend on where I start, which is what I need to determine

When I veiw this all I get is an immensely huge list of HMTL.

View 5 Replies
View Related
Jul 8, 2014

I'm having problem importing a CSv file into Excel. The CSV file is around 4-5k lines.

The layout is similar to this :

H1, H2, H3, H4, H5, H6

A4, A3, dummy, Dummy, ~,

A4, A3, dummy, "Dummy ,Dummy", ,

[Code]....

View 4 Replies
View Related