Formulas

ejacksonBPT

Is there a way I can calculate on a metric sheet the number of Sites completed within the last 3 months?

Paul Newcome

You should not need to make any adjustments as it is built off of today's date.

Michelle Choate 2

On your metric sheet you would have the primary column have the different word choices of the status. Then your formula would simply be =COUNTIF({Order Tracker Status}, Primary@row)

ejacksonBPT

but is there way that would filter and count the dates completed to the last 3 months? (or certain months)

Paul Newcome

Do you want past three months from today, a specific set of months, or a specific month?

ejacksonBPT

I'd want to show the past 3 full months.

If today was November 20th I'd like to show Aug, Sept, Oct.

Paul Newcome

Ok. Try something along the lines of:

=COUNTIFS({Date Range}, AND(@cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 9, 1)), @cell <= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

The above will go back to the first of three months ago and up to the last of the previous month.

ejacksonBPT

Genius! that is a very complex formula. So say its December 2nd. The same formula will auto calculate Sept, Oct, Nov ? Or do I need to make any adjustments?

Paul Newcome

You should not need to make any adjustments as it is built off of today's date.

ejacksonBPT

Hello, i have an additional request for this same situation. Is there a way to show the breakdown of each month and have the current month displayed in graph and each of the pas three months? And have the months updated based on whatever "todays" date is?

Like display orders completed for the current month - November. Also show orders completed for August. Orders completed for Sept. Order completed for Oct.

Then when its December have the data auto change to show orders completed in Dec. Orders completed for Nov. Orders completed in Oct.

Paul Newcome

For this I would suggest inserting a text/number column in your calc sheet. This column would have this going down the rows

3

2

1

0

Then your COUNTIFS would change to this (put in each row it should still be dynamic):

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = IF(MONTH(TODAY()) <= Number@row, (12 - Number@row) + MONTH(TODAY()), MONTH(TODAY()) - Number@row), IFERROR(YEAR(@cell), 0) = YEAR(TODAY()) - IF(MONTH(TODAY()) <= Number@row, 1)))

We can also write out a formula to create chart labels that would be dynamic. Feel free to let me know if you need that one and how you would want the labels to look.

ejacksonBPT

I really appreciate all your help!

What in your above formula am I changing and/or referencing other cells? Is it "Date Range". What am I relacing that with?

Yes chart labels would be great. If using a bar chart I would need the month names across axis X and then the Y axis would be "New Circuits Completed"

Paul Newcome

You would keep the same range that you are already using. It is the criteria (bold characters) that changes.

For the labels, would you want full month name, month abbreviation, full year, last two of the year, no year, etc…?

Jan. '24

Jan 24

January 2024

Jan

Jan.

January

so on and so forth. What is the format of the actual labels themselves.

ejacksonBPT

The months can be like

January
2024

Paul Newcome

In that case I would suggest a helper column in the calc sheet that has each month listed with January at the top and December at the bottom.

Then you would use

=INDEX([Month Text]:[Month Text], IF(MONTH(TODAY()) <= Number@row, (12 - Number@row) + MONTH(TODAY()), MONTH(TODAY()) - Number@row)) + " " + YEAR(TODAY()) - IF(MONTH(TODAY()) <= Number@row, 1)

ejacksonBPT

What am I doing wrong?

Paul Newcome

I missed a set of parenthesis. GIve this a try:

=INDEX([Month Text]:[Month Text], IF(MONTH(TODAY()) <= Number@row, (12 - Number@row) + MONTH(TODAY()), MONTH(TODAY()) - Number@row)) + " " + (YEAR(TODAY()) - IF(MONTH(TODAY()) <= Number@row, 1))