Need help with a Formula based on a cell we made contains Month and Year

1

I am trying to calculate our “Event Total” and the date is on another sheet:

image
image651×574 43.9 KB

Trying to see how we could calculate these months total using a row in our “Past Orders” sheet that has a “Total” cell.

If I did not explain it well, please feel free to ask me questions.

Thank you!

2

Hi @Renee_O

Not sure I understood 100% but I’ll try, assuming that you want to summarize data from an Event table to Team Event Stats table

I created those 2 tables, using a true real Date Column in Team Event Stats, and using MonthName()
formula if you prefer to display plain text.

image
image1255×616 57 KB

I assigned random dates to Event’s event !

In a new “Total” column in Team Event stats, I will filter Events for them to match :

  • current month
  • current year

that will give me a list of event

image
image1227×586 76.4 KB

Finally, just Count() the number of events like this :slight_smile:

image
image1217×561 70.8 KB

Is that want you wanted to achieve ?

Cheers

Quentin

3

Thank you, we are on the right track! What we wanted to achieve is

  • the total $$$ of the event instead of count. How should we do this?
  • and should be based on a row of a cell which we put (Jan 2022, Feb 2022) in another sheet so we wanted to use the data from the other sheet to calculate the total $$$ based on the month (Jan 2022, Feb 2022)

Let me know if it’s not that clear so I can send photos.

4

Considering you’ve got an Amount column in your event table :

image
image1490×638 93.8 KB

Sorry, I did not understand this one :slight_smile:

5

No worries, here you go!

We have this stats table:

image
image738×664 44.9 KB

If we want to put the total $$$ we got from Jan 2022, we’ll need the details from this different sheet:

How do we make that a formula for all the succeeding months?

6

OK, that’s what I explained in my previous post

My table EVENTS is like your table PAST ORDERS
and my column AMOUNT si like your column TOTAL

So if I rename my tables, here you go :

image
image1437×700 95.4 KB

7

Thank you! Can we change the formula by basing it on the month format “January 2022” because the rows has “January 2022” etc as well.

8

Hi @Renee_O
this is exactly the same principle, just filter with Contains() function, but honestly I would not recommand it, as this is not as scalable as using Month() and Year() function.

i’ll let you adapt the filter formula then !

9

Ohh awesome! I have figured it out. Your help is much appreciated!

1 Like