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.
NumbersOpenedClosedYes/noOpen monthClose monthTypeThis the presentation of count of tickets
INC10212211-03-2018 11:0011-04-2018 00:00FALSEMarchAprilINCMonthsINCRITTASARC
INC10212312-03-2018 11:0012-04-2018 00:00FALSEMarchAprilINCMay
INC10212402-01-2018 00:0002-02-2018 00:00FALSEJanuaryFebruaryINCJune
INC10212502-01-2018 00:0002-02-2018 00:00FALSEJanuaryFebruaryINCJuly
INC10212617-10-2017 00:0017-12-2017 00:00FALSEOctoberDecemberINCAugust
INC10212707-05-2017 00:0007-07-2017 00:00FALSEMayJulyINCSeptember
INC10212817-11-2017 00:0017-01-2018 00:00FALSENovemberJanuaryINCOctober
INC10212916-05-2017 00:0016-07-2017 00:00FALSEMayJulyINCNovember
INC10213002-01-2018 00:0002-03-2018 00:00FALSEJanuaryMarchINCDecember
INC10213112-01-2018 00:0012-03-2018 00:00FALSEJanuaryMarchINCJanuary
INC10213216-05-2017 00:0016-07-2017 00:00FALSEMayJulyINCFebruary
RITM145822-09-2017 00:0022-11-2017 00:00FALSESeptemberNovemberRITMarch
RITM145923-07-2017 00:0023-09-2017 00:00FALSEJulySeptemberRITApril
RITM146025-06-2017 00:0025-08-2017 00:00FALSEJuneAugustRIT
RITM146114-12-2017 00:0014-02-2018 00:00FALSEDecemberFebruaryRIT
RITM146202-07-2017 00:0002-08-2017 00:00FALSEJulyAugustRIT
RITM146308-01-2018 00:0008-01-2018 00:00TRUEJanuaryJanuaryRIT
RITM146411-06-2017 00:0011-06-2017 00:00TRUEJuneJuneRIT
TASK546801-01-2018 00:0001-02-2018 00:00FALSEJanuaryFebruaryTAS
TASK546907-01-2018 00:0007-02-2018 00:00FALSEJanuaryFebruaryTAS
TASK547016-07-2017 00:0016-08-2017 00:00FALSEJulyAugustTAS
TASK547113-12-2017 00:0013-01-2018 00:00FALSEDecemberJanuaryTAS
TASK547221-11-2017 00:0021-12-2017 00:00FALSENovemberDecemberTAS
TASK547329-07-2017 00:0029-08-2017 00:00FALSEJulyAugustTAS
ARCT465819-05-2017 00:0019-06-2017 00:00FALSEMayJuneARC
ARCT465926-10-2017 00:0026-11-2017 00:00FALSEOctoberNovemberARC
ARCT466011-11-2017 00:0011-12-2017 00:00FALSENovemberDecemberARC
ARCT466112-08-2017 00:0012-09-2017 00:00FALSEAugustSeptemberARC
ARCT466207-08-2017 00:0007-09-2017 00:00FALSEAugustSeptemberARC
ARCT466302-11-2017 00:0002-12-2017 00:00FALSENovemberDecemberARC
ARCT466414-09-2017 00:0014-10-2017 00:00FALSESeptemberOctoberARC
ARCT466503-06-2017 00:0003-07-2017 00:00FALSEJuneJulyARC
ARCT466631-07-2017 00:0031-07-2017 00:00TRUEJulyJulyARC

<colgroup><col><col span="2"><col><col><col span="2"><col><col span="7"></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col><col><col span="2"><col><col span="5"></colgroup><tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
258
amphead
A
P
  • Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
380
GlennUK
G
L
  • Question
Formula Help with Count Number of Vessel Turnaround Trips monthly based on Criteria
Replies
1
Views
252
Lukma
L
Cubist
  • Solved
Dynamic Report
Replies
8
Views
259
Peter_SSs
Peter_SSs
airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
803
airforceone
airforceone

Forum statistics

Threads
1,215,545
Messages
6,125,450
Members
449,227
Latest member
Gina V

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