Counting previous months data

dave.mcpherson56751
dave.mcpherson56751 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, i am looking for some help please.

I have been reporting using today and looking back 30 days, however my business now wants me to report on the previous months data. so if in Mid June it looks at May's data

Is this possible to have as a formula?

Thank you

Dave

 

Tags:
«1

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is possible. Could you describe your setup in more detail and possibly provide some screenshots?

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

     

    the set up is we capture data via form entries, capturing dates and types of incidents reported. 

    The formula used for the 30 days is:

    =COUNTIFS({Nationwide Helpdesk Range 1}, <=TODAY(), {Nationwide Helpdesk Range 1}, >=TODAY(-30), {Nationwide Helpdesk Range 2}, [Primary Column]1)

    The primary column being the issue type, more than happy to share the sheet with you to take a look.

    Regards

     

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Give this a go.

     

    =COUNTIFS({Nationwide Helpdesk Range 1}, MONTH(@cell) = MONTH(TODAY()) - 1, {Nationwide Helpdesk Range 2}, [Primary Column]1)

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi,

     

    thanks for coming back to me, i have tried this but get "Invalid data type"

    I have tried to attached two files showing where the data is being searched which hopefully helps.

     

    Again, thank you for taking the time to look into and help with this.

    Dave

    Slide1.PNG

    Slide2.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What data is in range 1?

     

    There are two possible fixes for this...

     

    1. Switch the range/criteria sets so that the date is being referenced second.

    =COUNTIFS({Nationwide Helpdesk Range 2}, [Primary Column]1, @{Nationwide Helpdesk Range 1}, MONTH(@cell) = MONTH(TODAY()) - 1)

    .

    2. If there are any cells within the range that are blank or contain text, we need to account for that as well.

    =COUNTIFS({Nationwide Helpdesk Range 2}, [Primary Column]1, {Nationwide Helpdesk Range 1}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1)

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

    that seems to have done it, wow that is amazing thank you so much.

    Regards

    Dave

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

    just as a thought, will this formula work in January looking back at December?

    Regards

     

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No it will not. To accomplish that we will need to use an IFERROR on the MONTH(TODAY()) portion as well.

     

    =COUNTIFS({Nationwide Helpdesk Range 2}, [Primary Column]1, {Nationwide Helpdesk Range 1}, IFERROR(MONTH(@cell), 0) = IFERROR(MONTH(TODAY()) - 1, 12))

     

    MONTH(TODAY()) - 1 will throw an error when you are in the month of January because there is no month number of zero. So what we do is use that to our advantage and replace the error with the number 12.

     

    From here, the only additional thing you will need to watch is if there are multiple years on the same sheet. If this is (or could be) the case, let me know, and we will tackle that as well.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

     

    Thank you again, if possible i would like to add the "across years" option as this will future proof the formula and i can leave it running.

    Amazing help thank you

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Sure thing. I have a pretty full schedule today, but I'll get it out here to you when I can. I haven't needed that particular solution in a while, so I am going to have to do some testing and work it out again.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/04/19

    Ok. So during testing I discovered that the MONTH(TODAY()) - 1 portion is actually not going to work. I typically use it inside of a DATE function which is where it WILL throw an error, but as a standalone, it will produce the zero and not throw an error. I have corrected this and taken into account the year for you...

     

    =COUNTIFS({Nationwide Helpdesk Range 2}, [Primary Column]1, {Nationwide Helpdesk Range 1}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), IFERROR(YEAR(@cell), 0) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))))

    .

    You will see I used two separate IF functions within the AND function for the date criteria.

     

    The first IF basically says that if the month of today equals 1, use 12, otherwise use today's month minus 1.

     

    The second if is very similar except for looking at the year. If today's month is 1, then look at today's year minus 1, otherwise use today's year.

     

    What this does when used in conjunction with the AND function is basically says that if today's month is 1, then look at the month number of 12 with last year's year, otherwise look for the month before today within the same year as today.

     

    Fell free to plug in some dates for testing and let me know how it goes.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

    Thank you so much for taking precious time to solve this for me, I have done some testing and everything looks to work perfectly.

    What a wonderful help, thank you again.

    Regards

    Dave

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    I stay on the notification for all of the threads I have responded to, so if something stops working or needs tweaked, feel free to get back on here and update.

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul,

    i have been asked for another scenario relating to this, the above solution you kindly came up with stays the same, however the now want to break down the category "Reactive Clean" into priorities, e. g "P1", "P2", "P3".

    I thought that this would be simple and just add this to the end of the formula but it does'nt appear to be the case.

    The below is what i tried but got "Incorrect Argument"

    =COUNTIFS({Nationwide Helpdesk Range 2}, [Primary Column]18, {Nationwide Helpdesk Range 1}, AND(MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR(@cell) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) - 1, YEAR(TODAY()), {Nationwide Helpdesk Range 3}, "P1")))

    Any help would be greatly appreciated.

    Thank you

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!