Count multiple citeria in a date range

Z

zack8pooh

New Member
Joined
Apr 10, 2014
Messages
2
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.

[TABLE="width: 1016"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Team</SPAN>[/TD]
[TD]Task</SPAN>[/TD]
[TD="colspan: 6"]Date</SPAN>[/TD]
[/TR]
[TR]
[TD]Team</SPAN>[/TD]
[TD]Task</SPAN>[/TD]
[TD]Date</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Today</SPAN>[/TD]
[TD="colspan: 2"]Next 2 -3 days</SPAN>[/TD]
[TD="colspan: 2"]Next 4 - 6 days</SPAN>[/TD]
[TD]> 6 days</SPAN>[/TD]
[/TR]
[TR]
[TD]Alpha</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/11/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]Inbound</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie</SPAN>[/TD]
[TD]Outbound</SPAN>[/TD]
[TD]4/12/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Outbound</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Delta</SPAN>[/TD]
[TD]Data</SPAN>[/TD]
[TD]4/10/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Data</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alpha</SPAN>[/TD]
[TD]Outbound</SPAN>[/TD]
[TD]4/12/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Charlie</SPAN>[/TD]
[TD]Inbound</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Alpha</SPAN>[/TD]
[TD]Outbound</SPAN>[/TD]
[TD]4/15/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Outbound</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Delta</SPAN>[/TD]
[TD]Data</SPAN>[/TD]
[TD]4/13/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Data</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/14/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]Delta</SPAN>[/TD]
[TD]Inbound</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/11/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Outbound</SPAN>[/TD]
[TD] [/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Charlie</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/12/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Data</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD="colspan: 2"] [/TD]
[TD="colspan: 2"] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Delta</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/13/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alpha</SPAN>[/TD]
[TD]Inbound </SPAN>[/TD]
[TD]4/10/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Today = 4/10/2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL span=3><COL><COL span=6></COLGROUP>[/TABLE]



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.
 
Sort by date Sort by votes
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
ABCDEFGHIJ
TeamTaskDate
AlphaInbound
Outbound
Data
CharlieInbound
Outbound
Data
DeltaInbound
Outbound
Data

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FCE4D6"]Team[/TD]
[TD="bgcolor: #FCE4D6"]Task[/TD]
[TD="bgcolor: #FCE4D6"]Date[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6"]Today[/TD]
[TD="bgcolor: #FCE4D6"]Next 2 -3 days[/TD]
[TD="bgcolor: #FCE4D6"]Next 4 - 6 days[/TD]
[TD="bgcolor: #FCE4D6"]> 6 days[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF"]Alpha[/TD]
[TD="bgcolor: #FFFFFF"]Inbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/11/2014[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF"]Charlie[/TD]
[TD="bgcolor: #FFFFFF"]Outbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/12/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF"]Delta[/TD]
[TD="bgcolor: #FFFFFF"]Data[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/10/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF"]Alpha[/TD]
[TD="bgcolor: #FFFFFF"]Outbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/12/2014[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #DDEBF7"]Alpha[/TD]
[TD="bgcolor: #DDEBF7"]Outbound[/TD]
[TD="bgcolor: #DDEBF7, align: right"]4/25/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF"]Delta[/TD]
[TD="bgcolor: #FFFFFF"]Data[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/13/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #DDEBF7"]Charlie[/TD]
[TD="bgcolor: #DDEBF7"]Inbound[/TD]
[TD="bgcolor: #DDEBF7, align: right"]4/25/2014[/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF"]Charlie[/TD]
[TD="bgcolor: #FFFFFF"]Inbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/11/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF"]Charlie[/TD]
[TD="bgcolor: #FFFFFF"]Inbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/12/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF"]Delta[/TD]
[TD="bgcolor: #FFFFFF"]Inbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/13/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF"]Alpha[/TD]
[TD="bgcolor: #FFFFFF"]Inbound[/TD]
[TD="bgcolor: #FFFFFF, align: right"]4/10/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Today =[/TD]
[TD="align: right"]4/10/2014[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]=COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"="&$G$13)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"<="&$G$13+2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"]=COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,"<="&$G$13+6)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J3[/TH]
[TD="align: left"]=COUNTIFS($A$3:$A$13,LOOKUP("zzzz",$E$3:E3),$B$3:$B$13,$F3,$C$3:$C$13,">"&$G$13+6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
ABCDEFGHIJ
1TeamTaskDate*TeamTaskTodayNext 2 -3 daysNext 4 - 6 days> 6 days
2AlphaInbound04/11/2014*AlphaInbound1100
3CharlieOutbound04/12/2014*AlphaOutbound0101
4DeltaData04/10/2014*AlphaData0000
5AlphaOutbound04/12/2014*CharlieInbound0201
6AlphaOutbound04/25/2014*CharlieOutbound0100
7DeltaData04/13/2014*CharlieData0000
8CharlieInbound04/25/2014*DeltaInbound0010
9CharlieInbound04/11/2014*DeltaOutbound0000
10CharlieInbound04/12/2014*DeltaData1010
11DeltaInbound04/13/2014*******
12AlphaInbound04/10/2014*Today =04/10/2014****
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Converting Time Start/Stop Ranges to Presence/Absence
Replies
6
Views
294
myall_blues
myall_blues
E
  • Question Question
VBA code clean up
Replies
9
Views
699
mumps
mumps
B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
803
Peter_SSs
Peter_SSs
E
  • Solved
Count occurrences of dates across a range
Replies
3
Views
525
ellison
E
Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
965
Dave Patton
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top