Count Values between dates and multiple criteria

[vpapolu24]

Hi All,

I am trying to create a excel dash board that will show my work for the past week. I have 2 sheets, the dashboard (sheet 1) and the raw data sheet 2.

Summary

1)

In sheet 2 I have a column of case ID's (A) and adjacent to them I have a column of a "submit date" (B)

What I need to do is count all the the cases that have a submit date of between 9-29-2013 and 10-5-2013. I should be able to drag this formula to the adjacent cell so
it will calculate all the cases that have been submitted next week as well.

2)

I need a count of all the case id's that are between 9-29-2013 through 10-5-2013 in the UPDATE DAte column (column C) AND the status column is "closed"

3)

I need a count of all the cases from 01-01-2011 through 10-5-2013 and the status column is NOT closed.

Any help will be greatly appreciated!!

I have attached a sample work book as well

[newdoverman]

This is the "form" of the formula to get what you want:

=COUNTIFS('Raw Data'!$J$2:$J$328,">="&H8,'Raw Data'!$J$2:$J$328,"<="&I8)

Your data has problems.

The dates in Raw Data $J$2:$J$328 and Raw Data $O$2:$O$328 are all text and have to be converted to real dates. You can do this by inserting helper columns next to columns J and O.

In the new column K enter this formula and copy down the length of the data:

=DATE(YEAR(J2),MONTH(J2),DAY(J2))

Format as yyyy/mm/dd

Copy column K and Paste Values into column J and then delete the helper column. Repeat this for column P and copy and paste values back into column O.

The other problem that you have is that the dates have no data for the dates that you have specified. Start your dates earlier in the year and you won't run into errors until the dates don't have data. As it is, you can't check the formulae for correctness because they will always produce an error.

To eliminate the errors in your final formulae enter the formulae like this:

=IFERROR(COUNTIFS('Raw Data'!$J$2:$J$328,">="&H8,'Raw Data'!$J$2:$J$328,"<="&I8),"")

[vpapolu24]

Hi newdoverman,

Thanks for the help! I figured out how to convert the text dates. Therefore I figured out part one of my question, however part 2 is where I need to find:

All the cases that have been updated (updated column, Column O) between days 9/25/2013 - 10/5/2013 that have the status (column Q), as closed. is there a spesific formula for this?

To find all the dates assigned for part 1 of the question, I assigned a start date and a end date column, and then used the sum products formula listed below:

=SUMPRODUCT((Sheet2!$J$1:$J$337>=G7)*(Sheet2!$J$1:$J$337<=G8))

Here is an updated view of my worksheet. Thanks again for the help!! I really appreciate it.

[newdoverman]

I can't find a column for Opened and assumed that it was column Q but that isn't right. Just change the Q in the formula to the column that is correct and it should work.

[newdoverman]

If the data is going to grow and you will be producing reports based on new data and maybe reports on old data as well, perhaps you would want to consider handling your data this way. Instead of SUMPRODUCT, use COUNTIFS as it is quicker and use named ranges based on a table of your data. The ranges will expand with new data.

[vpapolu24]

Thanks again! I ended up using the sum product function, and I figured out how to export my data into a csv file so the dates are all formatted correctly.

[newdoverman]

Very good!

Thanks for the feedback.