Count Based on Text & Date Range

Question

I'm trying to count text if it matches a certain criteria, example "One" then based on that text I am trying to determine how many of those occurred within a time frame, example "10/1/2013-12/31/2013"

Please help!

Answer 1

Kindly try this one 

=+COUNTIFS(B2:B15,">=3/1/2014",B2:B15,"<=10/1/2014",C2:C15,"one")

Answer 2

Hi,

I would use SUMPRODUCT like this

=SUMPRODUCT((A1:A20="One")*(B1:B20>=C1)*(B1:B20<=D1))

Where C1 is the earlier date and D1 is the later one.

Answer 3

Hi,

Try this

=SUMPRODUCT(('Order Management_HTO'!$I$2:$L$100="Churchill 08")*('Order Management_HTO'!$A$2:$A$100>=DATE(2014,4,1))*('Order Management_HTO'!$A$2:$A$100<=DATE(2014,4,22)))

Hope this helps.

Answer 4

It's not working however I'm wondering if my date format is set up right.  I have the dates listed as they are formatted on the sheet, ex: 10/1/2013 would by my earlier date & 12/31/2013 would be my later date.  Is there a different format that you have to set the date up with in formulas like this? 

Thank you for your help.  I appreciate it.

Hi,

The formula I gave you will work on any properly formatted date. Dates in Excel are numbers and all formatting does is change what you see on the worksheet, the underlying value remains a number.

I would check these dates to ensure they are in fact dates and not text that looks like a date. Let's assume the dates are in Col B, put this in an unused column and drag down

=isnumber(b1)

If they are real dates then that will return TRUE, if it returns FALSE then they're not dates.

Answer 5

It's not working however I'm wondering if my date format is set up right.  I have the dates listed as they are formatted on the sheet, ex: 10/1/2013 would by my earlier date & 12/31/2013 would be my later date.  Is there a different format that you have to set the date up with in formulas like this? 

Thank you for your help.  I appreciate it.