Counting previous months data

dave.mcpherson56751

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

 

Paul Newcome

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

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

Ok. Give this a go.

 

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

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

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

Hi Paul,

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

Regards

Dave

Paul Newcome

Happy to help! 

dave.mcpherson56751

Hi Paul,

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

Regards

 

Dave

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

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

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

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

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

Happy to help! 

 

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

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