Count if

C

claramjo

New Member
Joined
Mar 10, 2018
Messages
2
Hi All,

Help me!

I want to count the ticket numbers with following conditions.
1)Count should be according to the month and the ticket type
2)If the ticket is opened ex: March and Closed in June then the ticket is carried forward to April, May and ultimately closed in June. So the count for April is +1, May is +1 and so on.
Please let me know if there are any questions.
Below is the data and requirement on right side.
[TABLE="width: 1155"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col span="7"></colgroup><tbody>[TR]
[TD]Numbers[/TD]
[TD]Opened[/TD]
[TD]Closed[/TD]
[TD]Yes/no[/TD]
[TD]Open month[/TD]
[TD]Close month[/TD]
[TD]Type[/TD]
[TD][/TD]
[TD="colspan: 4"]This the presentation of count of tickets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102122[/TD]
[TD="align: right"]11-03-2018 11:00[/TD]
[TD="align: right"]11-04-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]Months[/TD]
[TD]INC[/TD]
[TD]RIT[/TD]
[TD]TAS[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102123[/TD]
[TD="align: right"]12-03-2018 11:00[/TD]
[TD="align: right"]12-04-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]May[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102124[/TD]
[TD="align: right"]02-01-2018 00:00[/TD]
[TD="align: right"]02-02-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]June[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102125[/TD]
[TD="align: right"]02-01-2018 00:00[/TD]
[TD="align: right"]02-02-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]July[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102126[/TD]
[TD="align: right"]17-10-2017 00:00[/TD]
[TD="align: right"]17-12-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]October[/TD]
[TD]December[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]August[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102127[/TD]
[TD="align: right"]07-05-2017 00:00[/TD]
[TD="align: right"]07-07-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]May[/TD]
[TD]July[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD="colspan: 2"]September[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102128[/TD]
[TD="align: right"]17-11-2017 00:00[/TD]
[TD="align: right"]17-01-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]November[/TD]
[TD]January[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]October[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102129[/TD]
[TD="align: right"]16-05-2017 00:00[/TD]
[TD="align: right"]16-07-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]May[/TD]
[TD]July[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD="colspan: 2"]November[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102130[/TD]
[TD="align: right"]02-01-2018 00:00[/TD]
[TD="align: right"]02-03-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]March[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD="colspan: 2"]December[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102131[/TD]
[TD="align: right"]12-01-2018 00:00[/TD]
[TD="align: right"]12-03-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]March[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]January[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INC102132[/TD]
[TD="align: right"]16-05-2017 00:00[/TD]
[TD="align: right"]16-07-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]May[/TD]
[TD]July[/TD]
[TD]INC[/TD]
[TD][/TD]
[TD]February[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1458[/TD]
[TD="align: right"]22-09-2017 00:00[/TD]
[TD="align: right"]22-11-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]September[/TD]
[TD]November[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD]March[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1459[/TD]
[TD="align: right"]23-07-2017 00:00[/TD]
[TD="align: right"]23-09-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]July[/TD]
[TD]September[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD]April[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1460[/TD]
[TD="align: right"]25-06-2017 00:00[/TD]
[TD="align: right"]25-08-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]June[/TD]
[TD]August[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1461[/TD]
[TD="align: right"]14-12-2017 00:00[/TD]
[TD="align: right"]14-02-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]December[/TD]
[TD]February[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1462[/TD]
[TD="align: right"]02-07-2017 00:00[/TD]
[TD="align: right"]02-08-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1463[/TD]
[TD="align: right"]08-01-2018 00:00[/TD]
[TD="align: right"]08-01-2018 00:00[/TD]
[TD="align: center"]TRUE[/TD]
[TD]January[/TD]
[TD]January[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RITM1464[/TD]
[TD="align: right"]11-06-2017 00:00[/TD]
[TD="align: right"]11-06-2017 00:00[/TD]
[TD="align: center"]TRUE[/TD]
[TD]June[/TD]
[TD]June[/TD]
[TD]RIT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5468[/TD]
[TD="align: right"]01-01-2018 00:00[/TD]
[TD="align: right"]01-02-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5469[/TD]
[TD="align: right"]07-01-2018 00:00[/TD]
[TD="align: right"]07-02-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5470[/TD]
[TD="align: right"]16-07-2017 00:00[/TD]
[TD="align: right"]16-08-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5471[/TD]
[TD="align: right"]13-12-2017 00:00[/TD]
[TD="align: right"]13-01-2018 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]December[/TD]
[TD]January[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5472[/TD]
[TD="align: right"]21-11-2017 00:00[/TD]
[TD="align: right"]21-12-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TASK5473[/TD]
[TD="align: right"]29-07-2017 00:00[/TD]
[TD="align: right"]29-08-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]TAS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4658[/TD]
[TD="align: right"]19-05-2017 00:00[/TD]
[TD="align: right"]19-06-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4659[/TD]
[TD="align: right"]26-10-2017 00:00[/TD]
[TD="align: right"]26-11-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4660[/TD]
[TD="align: right"]11-11-2017 00:00[/TD]
[TD="align: right"]11-12-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4661[/TD]
[TD="align: right"]12-08-2017 00:00[/TD]
[TD="align: right"]12-09-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4662[/TD]
[TD="align: right"]07-08-2017 00:00[/TD]
[TD="align: right"]07-09-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4663[/TD]
[TD="align: right"]02-11-2017 00:00[/TD]
[TD="align: right"]02-12-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4664[/TD]
[TD="align: right"]14-09-2017 00:00[/TD]
[TD="align: right"]14-10-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4665[/TD]
[TD="align: right"]03-06-2017 00:00[/TD]
[TD="align: right"]03-07-2017 00:00[/TD]
[TD="align: center"]FALSE[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARCT4666[/TD]
[TD="align: right"]31-07-2017 00:00[/TD]
[TD="align: right"]31-07-2017 00:00[/TD]
[TD="align: center"]TRUE[/TD]
[TD]July[/TD]
[TD]July[/TD]
[TD]ARC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1027"]
<colgroup><col><col span="2"><col><col><col span="2"><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Maybe this:

With the colum Months with Dates (with the format "mmmm").

In J3 and copy down and to the right

=SUMPRODUCT(--($G$2:$G$34=J$2),--($I3 > INT($B$2:$B$34)),--(EOMONTH($I3,0) < INT($C$2:$C$34)))

Markmzz
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
429
amphead
A
S
  • Question Question
Employee Training Tracker
Replies
0
Views
240
Schuyla
S
U
  • Question Question
VLOOKUP that will SUM data based on Date Range
2
Replies
18
Views
1K
UnitedCloud01
U
M
  • Question Question
Calc cumulative by month
Replies
6
Views
205
mylamly26
M
P
  • Question Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
557
GlennUK
G

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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