Capture Issues for the Past Month - Formula required.

03/30/19 Edited 12/09/19

Hi

Can someone please help me again :-(

I am trying to capture the number of issues per brand over the past Month.

For example for Queensland I have the below formula working (Number of issues in total)

=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]17)

I tried to capture for the past month (unsuccessful)

=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, >TODAY(-30))

Screenshot Below is the data i am trying to capture (its in a separate sheet to my metrics). Ideally i would like to capture all issues for the Month of March

Any assistance will be appreciated

Thanks

 

Capture-help.PNG

Comments

  • Nathan LloydNathan Lloyd Employee
    edited 04/01/19

    Hi there,

    You're very close to having this formula working as expected. You should be able to adjust your formula:

    • "=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, >TODAY(-30))"

    to instead make use of the MONTH() function to highlight the period you're wanting to count for.

    It also looks like you're wanting to count based on multiple criteria, so you'll need to use the COUNTIFS function instead. I'm guessing your "{Dev Drafting Help Range 1}" is for the "Date Requested" column.

    The updated formula would look something like this:

    • "=COUNTIFS({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, MONTH(@cell) = 3)"

    The "3" in this example denotes the number corresponding with the Month out of the year (March in this case). See my screenshot of an example attached. I'm not referencing another sheet, but hopefully this still conveys this well enough.

     

    Example

    Hope that helps! 

    Best,

    Nathan

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    First I am going to suggest a COUNTIFS instead of a COUNTIF. It allows you to build in multiple ranges and corresponding criteria. Then give something like this a go...

     

    =COUNTIFS({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, IFERROR(MONTH(@cell), 0) = 3)

     

    This will look at the dates and count only those that are in the month of March. I am assuming that the {Dev Drafting Help Range 1} range is referring to the date column.

Sign In or Register to comment.