# COUNTIF: 2 Criteria: Date Range Column & Text Column

Dec 8, 2005Col C = Text and Col F = dates

I would like to count the # of times a value occurs in Col C based on a date

range in Col F.

Col C = Text and Col F = dates

I would like to count the # of times a value occurs in Col C based on a date

range in Col F.

ADVERTISEMENT

I am trying to put together a formula to allow me to count the number of occurances dates within a month in a column.

So far I have tried

=COUNTIF(F4:F500,DATE(2011,10,9)) but it only lets me search for the 9th of October (when I want to count all dates specified in october.

Is there any other way I can search (count) a range for dates in October 2011 only?

I have a start date column and column for status. Status is either "in-progress" or "complete". I want to count the number of "complete" items that fall within a specific date range. I have tried countif but I can only seem to get a count of all items that fall between the date range. I do not want a pivot table for this.

View 3 Replies View RelatedI am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...

=COUNTIF(G1:G100,"AND(>10,<20)")

I have a 2 groups of column headings with a different month and year in each heading so

1st Group of columns range

Columns AJ through AX

Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"

2nd Group of columns range AY though CE

Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"

Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"

Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"

Because the ranges keep changing month over month, how do i do this.

I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A.

Here is what I have but it is counting the dates in col A not the order numbers in B?

I am trying to Count (or counta?) cells in one column if they have data but only if within a date range in another column.

So, in the example below, I would want to count how many cells in column E have data in them, but only if the date in column A falls between Jan 1 2014 and Jan 5 2014. (In this case, result should be 4).

A

B

C

D

E

01/01/2014

[Code].....

I have 2 columns: Locations (Column A) & Dates (Column B).

I want to find the most recent date for a selected Location (stored in $G$1).

currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).

I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.

Is there some way to condense this into just 2 cells?

See attached file for example (note the columns are currently sorted by date, but that is not always the case).

I have the following code that fills a range of cells (starting at the active cell) with the date a user selects from a calendar control.

Private Sub Calendar1_Click()

ActiveCell.Value = Calendar1.Value

Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Selection.Copy Destination:=Sheets("Audit_Results_Data_Collection").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)....................

I'm still confused regarding a complex formula - rather n00b. Using Office 2007 with XP. Can't figure out with help or previous questions.

The following is what I would like this formula to accomplish. Picture included below.

Take all the values of column of C and add them together if and only if the corresponding date value in column A is during the month of January (and February, March, and so forth).

Event DateNameTotal Amount2/21/2009$4,5002/22/2009$04/3/2009$04/25/2009$9,0004/30/2009$8,3005/2/2009$9,0005/7/2009$5,5005/9/2009$9,0005/12/2009$3,000

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)

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function

Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

I am dealing with date ranges with the data being on a second tab of the Excel workbook. The first tab is to populate a report of the difference in dates. I need to count the number of values are less than 3, after I subtract Column P from Column K.

Raw Data'!P:P is a Particular date and 'Raw Data'!K:K is another date. I am trying to find a count of all of the rows where the difference of the 2 dates is less than 3.

Here is a couple formulas that I tried.

=COUNTIF(IF(('Raw Data'!P:P-'Raw Data'!K:K)

I would like to sum G column (in my sample) that will meet a date criteria in C (like from 10/9/2009 to 11/9/2009) or similar. I tried it already with a formula =IF(c2=DATEVALUE("10/9/2009")=<("11/9/2009"),G2,0) could not make it work but its okay without the =<, or a single entry date (a sample in AB column), with this formula its being transferred to another column and being summed, what am trying to figure out is to make a formula or function that it can be incorporated in a sum at the bottom of G column if possible, I would be able to check how much cash collected in a week, days or month....

View 4 Replies View RelatedI have a large database that is updated daily. From within the database I need to:

1. Select all records where date field A is 14 days or less than date field B

AND

2. Where a field C matches a text criteria, i.e., =DOGGIE

AND

3. Append KITTY to field D of all records that match criteria 1 and 2

Finally

4. SAVE results as a text file.

Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"

View 5 Replies View RelatedSo I have my countif if past a certain date, which works fine:

=COUNTIF(J89:J128,">="&Sheet1!$A$2)

but what I need is this...

How do I count... but only if a date is past a certain range AND the cell next to it is nonblank?

how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range?

For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria.

Eg., Column A holds fruit names:

Apple

Orange

Banana

Apple

and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.

I have my students down the far left column (30 rows) and along the top for each worksheet is the criteria which they must be able to do. If my in row 2 completes the criteria text above in column 5 for example, then I input a 3 which then adds the points and works out their sub level scores. However I am now in need of it being able copy the criteria above if a student on any row has a T(for target) in that box. I would then like that criteria to be copied to a master sheet which displays their name down the side and all the targets they have for that subject in the next column, so that they can be referenced for their report summary worksheet.

View 14 Replies View RelatedI have a drop down list called Status including Testable and Not Testable

A1 = Testable

A2 = Testable

A3 = Not Testable

A4 = =COUNTIF(A1:A3,"Testable")

Gives me 0... i don't get it. Should show 2 no?

Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY.

this is what I have come up with....

sumif(N10:N49, "Business", AW10:AY49)

but it returns incorrect values and not the whole range????

Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do...

H2 and H3 are my attempted formulas

I'm trying to count the number of incidents in column BB that are >0 but only IF the value in column E is "Abbeywood". i.e. how many times there's a figure greater than 0 for Abbeywood. I can't seem to get count if to do this!

View 4 Replies View RelatedIn worksheet "Details" search down column "A" looking for any text/numbers. If there is something entered in the cell then look at column "M" for the same row and if that cell is empty inset "0%" in the cell.

Example:

Cell "A1" contains 12345. Cell "M1" is blank. The macro would inset 0% in "M1".

Cell "A2" contains 54321. Cell "M2" contain 40%. The Macro does not modify that row.

Cell "A3" is blank. The macro does not modify that row.

Cell "A4" contain 65478. Cell "M4" is blank. The macro would inset 0% in "M4".

I need to add all cells within multiple ranges where the characters "A", "S" and "D" occur.

=SUMPRODUCT(COUNTIF(INDIRECT({"C22:C25","C28:C32"}),{"A","S","D"}))

I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.

Rolling 12 Months

IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on

1234Employee Name - - - - (11.07)

1235Employee Name - - - 0.20 -

1236Employee Name - - - - -

1237Employee Name - - - - (1.00)

[Code] .........

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

Is there any method to speed up a for each loop to count text in a cell with multiple criterias in a single column. This is on example:

Code:

For each rr in r

If rr = "a" And rr.Font.Strikethrough = False Or rr = "B" _

And rr.Font.Strikethrough = False Then

a = a + 1

end if

next

need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).

So now I just need it broken down by training type per month. How many x's in each column for February as an example.

formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.

View 4 Replies View RelatedI am trying to create an array formula which confirms whether a specific date falls within a list of date ranges. I have a list of start dates in column A and end dates in column B. Each row represents a date range. How can I write an array formula to check if a date falls within any of these data sets (i.e. date ranges)? I have attached a sample spreadsheet to show

View 7 Replies View RelatedAs part of a larger macro, the code below is supposed to start the separation of data into 4 columns. It doesn't work perfectly though and I have to manually inspect data and correct. The problem is that 22: 8- 3- 3 becomes 8- 3- 3 in the second column, which is what I want. However 60:12- 7-10 becomes 7/12/2010. Excel appears to recognise it as a date, when the extra number is in the second column.

View 4 Replies View RelatedI'm trying to create a SUMIF-style formula which is able to test for more than one condition. i.e if the values in column C meet a certain criteria and if the values in column E satisfy another criteria then add all the corresponding values in column D. In my particular spreadsheet, column C contains a date value. So, for all the dates in column C which are later than 01/01/2008 and for all the cells in column E which contain the word "oustanding", I need to add up all the corresponding values in column D.

View 3 Replies View RelatedHow to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs

View 3 Replies View RelatedI am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A

B

C

1

Cat 1

January 1, 2014

John

[Code] ..........

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;

C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1

C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1

C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

I have column A set up to display dates in the following format: 03/04/2009. While the "look" is fine for the values in the column, I cannot get it to stay in that format when it is moved to a text file. It simply converts the display back to 39876.

I would like a macro that 1) looks at all values in column A and 2) converts each cell in the column to text format but leaves the contents so they are dates 03/04/2009 (mm,dd,yyyy).

I need to custom sort a column. I have 3 different types of data in the column. First - multiple dates, Second - "TBA", and Third - "ASAP". What I need is when the column is sorted the "ASAP" rows will be first, the dates (sorted) will be next and finally the "TBA"s. I have been trying to use a custom list.

View 2 Replies View RelatedI am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:

ID

VOL

001

01

001

05

[code]....

The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique)

VOL (concatenated)

001

V01, V03, V05

002

V01, V04

003

V06

PS: I have people using this table with office 2003, so compatibility is necessary...

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4

ZZZ5.0015.0050.00130.00

ZZ5.0015.0050.00130.00

Z5.0015.0050.00130.00

YYY5.0015.0050.00130.00

YY5.0015.0050.00130.00

Y5.0015.0050.00130.00

DDD5.0015.0050.00130.00

DD5.0015.0025.0075.00

D5.0015.0020.0065.00

RRR5.0015.0015.0045.00

RR2.5010.0010.0030.00

R1.503.0010.0025.00

UUU0.751.505.0020.00

UU0.751.505.0020.00

U0.751.505.0020.00

P0.751.505.0020.00

T0.100.105.0018.00

CriteriaNumberValue

DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies View RelatedI'm working on a worksheet that has dates in one column (column A), and numbers in another column (column B). What I'm looking to do, is look through all of column A, find all entries between a certain date (ie Nov 1 07 - Nov 30 07), and sum all the values in column B that correspond those fields, as long as the value is greater than zero or not blank.

So, for example,

A B

Nov 1 1

Nov 6 -5

Nov 3 6

Dec 6 5

Jan 1 2

I would need the formula to return 7

Copyrights 2005-15 www.BigResource.com, All rights reserved