Count Strings/Text Within Date Range
May 2, 2008
I am very comfortable with VBA but I usually avoid using formulas if at all possible (unfortunately, I have, formulas are unavoidable (by request).. so formulas it is)
I will try to explain
Column A is a list of dates (ever expanding)
Column B is a list of names ( multiple entries of various names but aligned with various dates)
Column H is a list of names (each possible name which can appear in column B, no duplicates)
Cell I1 = A Date
Cell J1 = A Date
(forming a date range)
Basically what I need to do is:
= COUNTIF(B:B,H2) - but only count it if it is withing the date range (>=I1 & <=J1)
I have tried wrapping it in an IF statement but I can't seem to get it to work.
View 7 Replies
ADVERTISEMENT
Apr 22, 2013
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick
ballgame
stick ball
tenballs
green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
View 2 Replies
View Related
Aug 17, 2008
I have an excel file with 1000 of records for eg.
Date INITIALS CATEGORYACATEGORYB
31-Dec-07MTSVIN
01-Jan-08MSSVDI
04-Jan-08MORSKH
31-Jan-08MPSVHI
02-Feb-08MPRSIN
03-Feb-08MPVSIN
02-Jan-08MSRSIN
03-Mar-08MSKSKI
i want to count the datas
1) SELECTING IN THE SPECIFIED DATE RANGE (e.g from 1st jan to 31st Jan)
2) SELECTING MULTIPLE CRITERIA IN B COLUMN (MP & MS)
3) SELECTING MULTIPLE CRITERIA IN C COLUMN (SV & RS)
4) SELECTING MULTIPLE CRITERIA IN D COLUMN (IN & HI)
My colleagues tried countifs in excel 2007 but i have 2003. i want to specify all the four criteria in a single function to achieve my result (IN EACH MULTIPLE CRITERIA)
View 6 Replies
View Related
Jun 11, 2014
In Column A I have the list of strings that I need to search. I want to count the number of occurrences of these strings inside these two text files: Sample1.txt and Sample2.txt. For example I have the string "DOG". I want to search and count the number of times this string appeared in Sample1.txt, and Sample2.txt
See below:
View 5 Replies
View Related
Nov 17, 2012
I have a list of names in C1 down that I want to count. There could be hundreds of names in C1 and I don't want to sumif all of them. There must be a simpler solution
View 1 Replies
View Related
Sep 15, 2008
I have data in 2 columns.
COLUMN A COLUMN B
2008-01-01 00:00:00 CRIMINAL EVENT
2008-01-04 00:34:48 OTHER
2008-02-04 00:23:59 SUSPICIOUS INCIDENT
2008-01-31 23:59:59 ENEMY ACTION
2008-01-08 00:45:43 FRIENDLY ACTION
2008-01-09-00:45:33 RAID
I need a formula or macro that will count all the times "Criminal Event", "Other", "Suspicious Incident", and "Enemy Action" occured in January. Also if possible I need to not specify column ranges, such as A2:A7, but instead it needs to find the last cell containing data in Columns A and B, and use that as the end point. For example it would search A2:Last Cell Containing Data
View 9 Replies
View Related
Sep 27, 2006
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
View 9 Replies
View Related
May 9, 2008
Am trying to devise a formula that will allow me to extract a date (not stored in date format or recognizable by excel as such: "DEC1/09") and then manipulate it so that excel can recognize it and change it to a "1-Jan-01" form. Problem for me is that functions such as LEFT MID and RIGHT are very specific and sensitive obviously to any additional characters. some of my dates are preceded by "CAN BND 4.25/09"; "CANB BND 4.25/09"; "BC BND 4.25/09" and so on, you get the idea im sure that they are of differing lengths. The dates are equally strewn around as some (these are bond maturities) are 1st of the month while others may hold dates in the middle-end...15th, 30th, 31st etc.
is there a formula that will recognize the dates in the text strings regardless of string length and then a subsequent formula to manipulate the date to proper format?
i.e. "CAN BND 4.25/09 DEC1/09" and "CANB BND 4.25/07 JUN15/07"
Converted into: "01-Dec-09" and "15-Jun-07"
Not that the other parts of the string don't matter, already have macro that can recognize and rip bond coupons.
View 9 Replies
View Related
May 4, 2009
Im new to Macros. Im trying to find a string of text assign that to be the top of the data and then find a different string and assign that to be the bottom of the data. Then run a loop whilst inside that data range. Am i going about it the right way? Attached is a sample data file.
View 4 Replies
View Related
Mar 2, 2007
I receive data from different sources and one column is usually in upper case, but I prefer to use proper (title) case. The problem is that the data frequently contains text strings that should remain in upper case or should combine upper and lower case letters. Examples follow:
Data received:
COROLLA AE90, AE92
COROLLA SX, GTI
Data should look like:
Corolla AE90, AE92
Corolla SX, GTi
Note that 'GTi' is upper case 'GT' and lower case 'i'.
I've set up a spreadsheet 'CaseConverter.xls' as a look-up, with a range ('SpecialCase' - A2:A65536) featuring names, codes and acronyms represented correctly. Is it possible to use a macro to run Excel's global replace to search for upper case strings in one spreadsheet and replace with the correct strings from the other spreadsheet? The macro would have to loop through the entire 'SpecialCase' range and look in the supplied spreadsheet for versions of the data in the special case range without being case-specific. The supplied spreadsheet (see attached for example data) is called 'Application.xls'. Column B is what I'm trying to reach.
View 3 Replies
View Related
Apr 12, 2008
I have a column of data in the following format:
Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)
etc
I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.
For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.
I can do it in excel by:
(Assuming data is in cell V36)
'in colum x
=FIND("(",V36)
'in column y
=FIND(")",V36)
'in column z
=MID(V36,X36+1,Y36-(X36+2))
in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)
I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.
View 8 Replies
View Related
Jan 6, 2014
Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.
Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56
Account number:987654321
Account value as of 12/31/2013:$876,543.21
View 12 Replies
View Related
Jan 18, 2008
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
View 2 Replies
View Related
Dec 17, 2011
Is it possible to do the following with a formula in Excel...
I have a list of users with the dates they first logged into a system and the date they last logged in. I'm trying to group them together into segments so I can analyse them using a Pivot table and chart to see how often different groups are using the system, e.g. New customers in December, November, October, etc.
The trouble I am having is trying to convert the different human-readable text strings into a consistent number of weeks from today's date, e.g.
USER FIRST LOGGED IN LAST LOGGED IN
user 1 2 years 20 weeks 54 sec ago
user 2 44 min 7 sec 1 min 37 sec ago
user 3 49 weeks 2 days 17 min 3 sec ago
user 4 5 weeks 2 days 33 min 32 sec ago
user 5 38 min 9 sec 38 min 9 sec ago
user 6 5 weeks 3 days 1 hour 7 min ago
user 7 2 hours 17 min 2 hours 11 min ago
user 8 45 seconds ago 45 seconds ago
Is there a formula I can use to convert these human readble text strings into a number of weeks elapsed since today's date?
View 9 Replies
View Related
Dec 7, 2011
How would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
View 4 Replies
View Related
Jun 2, 2008
The D column is a date (formatted ddd d/m/yy) and the F column contains a string variable such as Red, Blue, Black (D2:D310 & H2:H310). I need a formula that can count how many times the word "Red" occurs on Monday, Tuesday, Wednesday, Thursday, Friday. I am showing the results in a table in which Q4:Q8 are the weekdays and R3:W3 are the names of the colours to be counted.
View 4 Replies
View Related
Sep 24, 2009
I have a problem if column A has 10 dates (some the same) i need to count the seperate dates ie if A1;A10 has 4 dates of 24-09-09 i need to total that many dates which is 4 to go into a cell named Thursday 23-09-09 will add up and go in wed and so on sorry no example no winzip in work.
View 3 Replies
View Related
May 6, 2008
I thought my formula would return the number sold (SLD) from column A, but it returns 0. Any idea where I'm messing up? {= SUMPRODUCT(--(A2:A30="sld"),IF(B2:B30>=D3,IF(B2:B30<=D4,A2:A30)))}
CREATE TABLES LIKE BELOW?
--A--- ----B----- -------C-------- ---D----
1 Status Close Date
2 SLD 04/26/07 Full Year Stats
3 SLD 04/27/07 From Date 04/20/07
4 SLD 05/02/07 To Date 04/20/08
5 SLD 05/03/07
View 4 Replies
View Related
Aug 21, 2008
I'm a novice trying to figure out the following:
I have a column, where each cell in the column has one of the following "ratings" entered as text:
excellent
good
fair
poor
How do I count the number of times "poor" AND "fair" are listed in the column? I used the following countif forumla to count the # of occurences of the word "poor":
=COUNTIF(SurveyData1stRoundSortedbyDoc!Y2:Y30,"poor")
...but how do I adjust the formula so that it counts the # of occurences of the word "fair" AS WELL?
View 9 Replies
View Related
Jul 24, 2014
I have a date range that I'm trying to get a total count on. The criteria is any date in COLUMN I that is not "approved" in COLUMN L and the date is less than the current date which I have in cell U5.
[Code] ......
View 12 Replies
View Related
Jan 20, 2012
Col A contains a list of random dates going back 1 year in this format 12-Oct-11. These dates are the dates of events. The events are coded into 5 areas in col B with a,b,c,d & e representing each typy of event.
I would like to look at the last 3 months of data taken from Col A and count how many events occured in that time period.
View 1 Replies
View Related
Nov 17, 2006
I have one worksheet with 2 columns: A - Dates, B - User IDs. In another worksheet I would like to have two columns: A - User IDs and B - Count. The count in Worksheet2, column B would be a count of how many instances the user ID in Worksheet1 appears before or on a certain date. Example:..................
View 2 Replies
View Related
Jul 17, 2007
I need to count the number of occurrences of a range of dates in one column. These are not unique dates, but dates between two date periods i.e. number of dates falling between 02/07/07 and 09/07/07. I have tried using the COUNTIFS function and inserting the column range (e.g. B7-B57) and then searching for dates <=02/07/07. However, what I need is to search for the number of times any date in a given period occurs i.e. number of 03/07/07s in a period from 02/07/07 to 09/07/07.
View 7 Replies
View Related
Mar 27, 2008
I've got a 2007 Excel spreadsheet that will count dates within a range using the COUNTIFS function. I am having to send this spreadsheet to users that only have Excel 2003. I need to know how to convert this so it works in 2003.
Since I'm doing this for a fiscal year I have to use a date range. Here are my two formulas I'm trying to convert:
=COUNTIFS(D9:D25,">3/1/08",D9:D25,"<6/30/08")
and
=COUNTIFS(D9:D25,">7/1/08",D9:D25,"<6/30/09")
View 4 Replies
View Related
Dec 9, 2009
I need to count text in a range.. Any text and only text.. No formulas.. No numbers..
This formula does not work
=IF(ISTEXT(B8),COUNTA(Data!G8,Data!J8,Data!M8,Data!P8,Data!S8,Data!V8,Data!Y8),"")
View 9 Replies
View Related
Mar 11, 2014
I want to use a sheet for planning work tasks. I need to count how many cells containing 'x' text exist in rows starting with dates greater than or less than 'Today'
If you look at my workbook (attached) I have dates and tasks on 'sheet1' I have stats on 'sheet2' In the 'spent' on sheet2 I have a COUNTIFS to count the cells containing 'Fish' but I also want to narrow it down further so that I see the nuber of cells containing 'fish' where the date in the A column for that row is less than 'Today'
View 3 Replies
View Related
Apr 3, 2012
I have a long list of products in an Excel column. I want to count the number of unique items in the list (the cells contain strings, not values).
I know I can use the "Remove Duplicates" tool in the Data menu, and then use a Count function on the resulting list. I wondered if there was an Excel function or formula that would do it, similar to the Frequency function that does the same thing with a list of values.
View 3 Replies
View Related
Nov 29, 2013
I have date range, create and fixed date as follows:
Create Date
Fixed Date
10/1/2013 17:36
10/25/2013 20:33
I have list of holidays as bellow:
1/1/2013
3/29/2013
4/1/2013
5/1/2013
[Code] .....
I need a result which shows how many holidays w/in the date range. The result i should be 1.
View 3 Replies
View Related
Oct 17, 2008
how to make a formula using a column label to count all cells that fall in a given date range. Example, in the attached sheet I would like to make a formula using the column heading "Dismantled" to count all cells that contain dates ranging from 10/13/08 through 10/19/08 inclusively.
View 5 Replies
View Related
Jan 7, 2010
I am needing to count the number of calls in a call center between certain hours by date. Column A is the date, Column B is the time of the call. I am needing to know how many calls occurred on X date between the hours of y and z. I have tried various forms of the countif and sumproduct but can't seem to get any of them to work for me. Here is the graph I am trying to populate:
Date Hours
12a-4a 4a-8a 8a-12p 12p-4p 4p-8p 8p-12a
12-7-09
12-8-09
12-9-09
12-10-09
12-11-09
View 9 Replies
View Related
