Month to date compare with other month to same day

[roadieoz]

Hi Everyone,

I am unsure how to word the question and because of this unsure if this has already been answered.
I have order data coming in via email > power automate > excel. The power automate is importing the data into a table in excel and captures the time received (example of format "21/01/2022 12:10:03 PM").
My question is, how can I see a monthly comparison of total orders to a certain day?

If today is April 15, I would like to compare with each month up to the 15th. So Jan 15, Feb 15.. etc.. if the day was April 20th, it would need to compare Jan 20, Feb 20.. etc up to the current April 20th
Is there a way that this would recognize what day and month it is and compare without changing the formula?

Thanks in advance!

 

[6StringJazzer]

It is possible but you need to show us what your data looks like and how you want your result displayed. You also mention "total orders" but give no information about how orders are recorded or counted. Is it one order per row?

As a quick example suppose that column A is the date and each row is one order. The month-to-date number of orders for January of this year is:

=COUNTIFS(A:A,">="&DATE(YEAR(TODAY()),1,DAY(TODAY()),"<"&DATE(YEAR(TODAY()),2,1))

 

[roadieoz]

Hi Jeff,

Yes sorry..

The table is 1 row per order and new orders are appended automatically by power automate. The formatting is controlled and never changes.
The goal is to be able to see the total number of orders to day this month, then be able to look and see the total numbers to the same day of the previous months and compare.

Here is a short example, unfortunately I cant share the file.

 

[6StringJazzer]

My formula will work for this, although change the reference to column A to be column D. You will need 12 formulas, one for each month. (Small error in above formula fixed here)

=COUNTIFS(D:D,">="&DATE(YEAR(TODAY()),1,DAY(TODAY()),D:D,"<"&DATE(YEAR(TODAY()),2,1))
                                      ^                                         ^
                                      1=Month of Jan                            2=Month of Feb