Count if

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
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

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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