Count multiple citeria in a date range

[zack8pooh]

Hi, i am trying to populate a table using a raw data to count using multiple criteria in a data range. Please refer to the attached file. Based on the team and task, i would like to count following a criteria to dertermine the number of occurrence based on the dates given. Also needed to search the count following a criteria for a specific date range.

Team</SPAN>

Task</SPAN>

Date</SPAN>

Team</SPAN>

Task</SPAN>

Date</SPAN>

Today</SPAN>

Next 2 -3 days</SPAN>

Next 4 - 6 days</SPAN>

> 6 days</SPAN>

Alpha</SPAN>

Inbound </SPAN>

4/11/2014</SPAN>

Alpha</SPAN>

Inbound</SPAN>

1</SPAN>

Charlie</SPAN>

Outbound</SPAN>

4/12/2014</SPAN>

Outbound</SPAN>

Delta</SPAN>

Data</SPAN>

4/10/2014</SPAN>

Data</SPAN>

Alpha</SPAN>

Outbound</SPAN>

4/12/2014</SPAN>

Charlie</SPAN>

Inbound</SPAN>

Alpha</SPAN>

Outbound</SPAN>

4/15/2014</SPAN>

Outbound</SPAN>

Delta</SPAN>

Data</SPAN>

4/13/2014</SPAN>

Data</SPAN>

Charlie</SPAN>

Inbound </SPAN>

4/14/2014</SPAN>

Delta</SPAN>

Inbound</SPAN>

Charlie</SPAN>

Inbound </SPAN>

4/11/2014</SPAN>

Outbound</SPAN>

Charlie</SPAN>

Inbound </SPAN>

4/12/2014</SPAN>

Data</SPAN>

1</SPAN>

Delta</SPAN>

Inbound </SPAN>

4/13/2014</SPAN>

Alpha</SPAN>

Inbound </SPAN>

4/10/2014</SPAN>

Today = 4/10/2014</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=3><COL><COL span=6></COLGROUP>

Example:
Team Alpha - Inbound - Date (4/10/2014) = 1
Team Charlie - Inbound - Date (4/10/2014) = 0

Team Delta - Outbound - Date (Next 4-6 day) = ?


I have tried multiple formulas but didn't work (Index/ Match, Vlookup, Countif, Sumproduct). Need you assistance on this especially on the date range.


Thank you.

 

[Weazel]

You might want to consider not using merged cells in your data because it makes writing formulas a little challenging.

As it is, I put todays date in separate columns.

I'm also kind of stuck at a better way of handling your date ranges so you probably need to play with that a little.

maybe someone else has a better way of handling the dates

Excel 2012

	A	B	C	D	E	F	G	H	I	J
1					Team	Task	Date
2	Team	Task	Date				Today	Next 2 -3 days	Next 4 - 6 days	> 6 days
3	Alpha	Inbound	4/11/2014		Alpha	Inbound	1	2	2	0
4	Charlie	Outbound	4/12/2014			Outbound	0	1	1	1
5	Delta	Data	4/10/2014			Data	0	0	0	0
6	Alpha	Outbound	4/12/2014		Charlie	Inbound	0	2	2	1
7	Alpha	Outbound	4/25/2014			Outbound	0	1	1	0
8	Delta	Data	4/13/2014			Data	0	0	0	0
9	Charlie	Inbound	4/25/2014		Delta	Inbound	0	0	1	0
10	Charlie	Inbound	4/11/2014			Outbound	0	0	0	0
11	Charlie	Inbound	4/12/2014			Data	1	1	2	0
12	Delta	Inbound	4/13/2014
13	Alpha	Inbound	4/10/2014			Today =	4/10/2014

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula G3 =COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"="&$G$13) H3 =COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"<="&$G$13+2) I3 =COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"<="&$G$13+6) J3 =COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,">"&$G$13+6) <tbody> </tbody>

<tbody>
</tbody>

 

[zack8pooh]

Thanks Weazel but i am not getting the desire result. Maybe i am playing it right.

 

[thisoldman]

There are more variations of sumproduct solutions that work than I show here. The formulas in G2:J2 are copied down the columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Team	Task	Date	*	Team	Task	Today	Next 2 -3 days	Next 4 - 6 days	> 6 days
2	Alpha	Inbound	04/11/2014	*	Alpha	Inbound	1	1	0	0
3	Charlie	Outbound	04/12/2014	*	Alpha	Outbound	0	1	0	1
4	Delta	Data	04/10/2014	*	Alpha	Data	0	0	0	0
5	Alpha	Outbound	04/12/2014	*	Charlie	Inbound	0	2	0	1
6	Alpha	Outbound	04/25/2014	*	Charlie	Outbound	0	1	0	0
7	Delta	Data	04/13/2014	*	Charlie	Data	0	0	0	0
8	Charlie	Inbound	04/25/2014	*	Delta	Inbound	0	0	1	0
9	Charlie	Inbound	04/11/2014	*	Delta	Outbound	0	0	0	0
10	Charlie	Inbound	04/12/2014	*	Delta	Data	1	0	1	0
11	Delta	Inbound	04/13/2014	*	*	*	*	*	*	*
12	Alpha	Inbound	04/10/2014	*	Today =	04/10/2014	*	*	*	*
Sheet