NEED HELP WITH A BUNCH OF FORMULAS IN A SHEET SUMMARY

Angie F

HI! Is there anyone in here that would be able to do a zoom call with me to set up a bunch of different formulas in a sheet summary? I have a bunch of different metrics that I need to do rolling comparisons for. Below is just a small example of what I'm needing to work out and I need them to pull specific information. I want to get this right in this first sheet I am working on as I will be needing to duplicate it for each of our various products our company sells. I need to compare the prior year to the current year, the previous week prior year to the previous week current year, the current week prior year to the current week current year, previous month prior year to the previous month current year, current month previous year to the current month current year. I have a metrics sheet and a raw data sheet I can reference. I want to set it up so it will automatically update the data every year as the years change.

Genevieve P.

@Angie F

This depends on what else is being updated. If your cells below are automatically bringing in data week-over-week, then the CHILDREN function will automatically roll that into the total, yes!

Lucas Rayala

Hi @Angie F, I can't do a zoom as I'm sort of casually answering questions when I have a chance (and many of the main answer providers are actually consultants, so a zoom call would need to be on the clock). However, maybe it would help if you provided specific details so that we could work though a single formula. Often the next formulas will just be a modification which you can work through (and if you get stuck, you can drop another question). Hope we can help!!!!

Angie F

@Lucas Rayala tell me what info you need and I'll screenshot it.

Lucas Rayala

Hi @Angie F -- just let us know what you want your formula to do and provide us with the sheets+columns that you want to obtain information from.

Angie F

@Lucas Rayala here is a screenshot of my raw data sheet:

Here are some of the questions I'm trying to answer:

I will basically need to answer the question for each of my columns and I need to single out one software program from the raw data sheet. I know I will need to use SUMIFS formulas but I can't seem to get them worded correctly. My summary page is on a different sheet designed just for each software program we need to track and that metric sheet is broken out by week and by month. It looks like this:

I want to work between the 2 sheets for the formulas if possible.

Angie F

@Genevieve P. would you be able to help me with some of these again? I have gotten some of them figured out thanks to Chat GPT but there are some that I'm still stuck on then. This one in particular is not working correctly. It just keeps showing me $0 and we have data entered so it should be showing me last weeks amount. The summary sheet is in my metrics sheet dedicated to this software program but is referencing the raw data sheet where all the data is entered.

Lucas Rayala

Hi @Angie F, regarding the above formula, a few things could be going wrong, but one sticks out. For range/criteria pair '{Date}' and 'WEEKNUMBER(TODAY())', the functions WEEKNUMBER(TODAY()) return an integer value corresponding to the week of the year. For example, because today is 19 Dec 2023 and this is the 51st week of the year, today (and this entire week) this pair of functions will return the number 51. That means your {Date} column must be a number corresponding to the actual week. If this is actually a series of dates, it won't return anything.

If you are trying to collect all the dates within this week, you would need to update this range/criteria pair. IF you don't have any future dates in the Date column, you could replace this criteria with a formula that calculates the first day of the week (and use the ">=" to make the criteria "any date greater than or equal to the first day of the week"). The formula to calculate the first Monday of the week is this:

TODAY() - WEEKDAY(TODAY(-2))

The WEEKDAY function converts the TODAY value to a number (1-7). Subtracting that from today's date gives the first Saturday of the week. Adding the -2 adjusts the first day to Monday. You can change the "-2" value to adjust what you want the first day to be. The final formula should look like this:

=SUMIFS({Correct Monday In}, {Date}, >=(TODAY()-WEEKDAY(TODAY(-2))), {Software}, "Golden Dragon")

Does this help?

Genevieve P.

Hey @Angie F

I agree with @Lucas Rayala! 🙂

Angie F

@Lucas Rayala that makes sense. The {Date} you see in the formula is the date that is entered in the sheet whenever the weekly data is entered and the formula is referencing that column. So should I just take that out then?

Lucas Rayala

Depends on what you are trying to sum. If you only want entries in the last week, use the formula I provided. If you need something else, let us know what you are trying to summarize.

Also, be careful with ChatGPT, especially if you don't know what the formula it provides is doing. Manually verify the answer it provides. I use it all the time, but it often provides incorrect answers for code and formulas.

Angie F

@Lucas Rayala I need to sum the money in column and I need to make sure it is only adding up the stuff for Golden Dragon and it is for the prior week of the previous year in one and then for the prior week in the current year for the other one.

I have noticed that with Chat GPT but it has helped my brain process how to get from point A to point B so I know which questions I need to ask.

I do have this metric sheet just for Golden Dragon. Would it be easier to answer the question of What is the Total Money in for the Prior Week of the Previous Year from here and make the formula so it will update each year?

Genevieve P.

Hey @Angie F

For your totals, what about simplifying this to just use

=SUM(CHILDREN())

In that grey row? Then you can reference that single cell in your sheet summary formula.

Angie F

@Genevieve P. if I do that will it update weekly without me having to do anything?

Genevieve P.

@Angie F

This depends on what else is being updated. If your cells below are automatically bringing in data week-over-week, then the CHILDREN function will automatically roll that into the total, yes!

Angie F

Thanks @Genevieve P. and @Lucas Rayala I finally got everything working the way I needed it to!

Angie F

@Lucas Rayala or @Genevieve P. I ran into one more snag. I'm getting the invalid operation error with this one. I need to count all of the Golden Dragon entries for 2023 for the prior week. Can you please help me with this one?