Count instances of a rows that falls between 2 dates

[Robb]

My formula does not work correctly because it doesn't count all instances from Sheet A and I'm not sure why.

Sheet B contains my Date Criteria & formula to count the instances:
A16 = 25 May 2015
B16 = 29 May 2015

=COUNTIFS(Sheet A!$J$5:$J$5000,">="&A$16,Sheet A!$J$5:$J$5000,"<="&B$16,Sheet A!$D$5:$D$5000,"Bug")

Sheet A contains all the raw data:
D = Types of Issue (Bug, Improvement, Task, etc)
J = Created Date

Why would it count 16 out of 27?

 

[Asala42]

The formula looks okay on the surface. I'd check the data again. Use a filter on the above example to isolate the 27 you expect. Some possibilities are:

- Some dates are being treated as text. The 16 might equate to those equal to the above 2 dates only for example.
- Some entries might be not actually exact (such as trailing spaces: "Bug " vs "Bug")

 

[Robb]

You are on to something.
- dates are in my raw data sheet are displaying like this 5/29/2015 2:47:00 PM, not sure if that makes a difference. When I formatted the column into a date format it still didn't resolve the problem. However, when I typed the actual date over the raw data date, my 27 count appeared. Must have something to do with the format of the date or is treated like a text....thanks!!

 

[Snakehips]

Robb,

I think you will find that it is the time element of your 'date' that is causing the issue.

Try the following that uses the integer of your date/time values so that the time element is ignored when testing >= & <=

Excel 2007
	C
16	5
Sheet B

Cell Formulas
Range	Formula
C16	=SUMPRODUCT((SheetA!$D$5:$D$5000="Bug")*(INT(SheetA!$J$5:$J$5000)>=A16)*(INT(SheetA!$J$5:$J$5000)<=B16))

Hope that helps.

 

[Robb]

Thanks Snakehips, time element on the date was the problem and your formula returns the correct value. Thanks for everyone's input and help!

 

[Robb]

I can't figure out how to use multiple criteria with SUMPRODUCT formula.

Sheet A contains all the raw data:
D = Issue Type (Bug, Improvement, Task, etc)
E = Issue Status (Open, In Progress, Reopened, Resolved, Closed)
J = Created Date (format = 5/29/2015 2:47:16 PM)

I need to count the following:
Count all Bug issues that are <> CLOSED and date is less than Jun 1st

D = "Bug" Where
E = "Open + In Progress + Reopened + Resolved" AND
J = Less Than 01-Jun-15

 

[Snakehips]

Robb,

For that scenario try......

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Date	Bug Count
2	01/06/2015	2
3
4				Issue	Status					Date
5				Bug	Open					25/05/2015
6				Task	Resolved					26/05/2015
7				Task	Open					03/01/2015
8				Improvement	Open					04/01/2015
9				Bug	Open					26/05/2015
10				Bug	Closed					26/05/2015
11				Task	Open					07/01/2015
12				Bug	Open					01/06/2015
SheetA

Cell Formulas
Range	Formula
B2	=SUMPRODUCT((SheetA!$D$5:$D$5000="Bug")*(INT(SheetA!$J$5:$J$5000))*(SheetA!$E$5:$E$5000<>"Closed"))

In basic terms, each bracketed element, separated by '*', of the SUMPRODUCT expression returns an array of values.
The '*' both acts as a logical AND and converts TRUE's and FALSE's within the arrays to 1's and 0's so as to allow a numeric result.
(Using a '+' to separate parameter arrays equates to a logical OR)

Hope that helps.