+ Reply to Thread
Results 1 to 5 of 5

Calculate number of tickets per month and charge a certain rate if it exceeds a threshold

  1. #1
    Registered User
    Join Date
    09-03-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Question Calculate number of tickets per month and charge a certain rate if it exceeds a threshold

    Trying to wrap my head around this.

    I have two rates:
    $10 - for less than or equal 5 tickets per month per task
    $20 - for more than 5 tickets per month per task.

    I understand how to calculate that with a simple CountIfs formula but the complication here is that I need to apply the $20 rate for all the tasks in that month if it exceeds 5. so some sort of Index/Match is needed.

    I attached a spreadsheet with the desired result and initial attempt.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,923

    Re: Calculate number of tickets per month and charge a certain rate if it exceeds a thresh

    Try this in F2:

    =IF(COUNTIFS(A:A,A2,B:B,">"&EOMONTH(B2,-1),B:B,"<="&EOMONTH(B2,0))>5,20,10)

  3. #3
    Registered User
    Join Date
    09-03-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculate number of tickets per month and charge a certain rate if it exceeds a thresh

    Edit.

    Question. How would I account for duplicate days?
    Last edited by WorkHard1; 09-09-2021 at 06:52 PM.

  4. #4
    Registered User
    Join Date
    09-03-2021
    Location
    USA
    MS-Off Ver
    365
    Posts
    11

    Re: Calculate number of tickets per month and charge a certain rate if it exceeds a thresh

    Edit.

    Question. How would I account for duplicate days? (don't count duplicate days)

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,923

    Re: Calculate number of tickets per month and charge a certain rate if it exceeds a thresh

    Try this:

    in D2:

    =if(COUNTIFS($A$2:A2;A2;$B$2:B2;B2;$E$2:E2;E2) = 1; maxifs($D$1:D1;$A$1:A1;A2;$E$1:E1;E2)+1;"")

    in F2:

    =if(maxifs($D$2:$D$16;$A$2:$A$16;A2)>5;20;10)

    Change ; to ,

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 12-04-2020, 03:06 AM
  2. Conditional Formatting when one date exceeds another by a threshold amount
    By jtfolk22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-25-2020, 04:22 PM
  3. Formula to calculate number of open support tickets per day
    By ranjit0610 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-23-2020, 09:22 AM
  4. MsgBox when cell value in column exceeds a threshold.
    By Stew1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2014, 02:41 PM
  5. Calculate number of closed tickets same day.
    By bandera in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-01-2013, 10:19 AM
  6. [SOLVED] Formula to calculate number of tickets.
    By bandera in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-23-2013, 01:45 PM
  7. Counting number of times a maximum exceeds a threshold on a certain frequency
    By varsakov in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-04-2013, 02:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1