SUMIFS help in summary panel

Options
This discussion was created from comments split from: Count If Current Week.
«1

Answers

  • Angie F
    Angie F ✭✭
    Options

    Hi all! I'm hoping someone can help me out. I have a sheet with these helper columns. It has a column with sales listed for each of our customers. I'm wanting to create a spot in our sheet summary that will show me the total sales for the current week in the sheet summary so I can add it to our dashboard. What formula would I use?



  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Angie F

    You can use the SUMIFS Function to SUM together the values in one column based on criteria in another.

    In your case, this would be that the week is Today's Week, or WEEKNUMBER(TODAY())

    Try something like this:

    =SUMIFS([Column to Sum]:[Column to Sum], [Week #]:[Week #], @cell = WEEKNUMBER(TODAY()))

    Keep in mind that the TODAY function needs to have the sheet opened/saved to be refreshed and recognize today's date. Here's more information: Automatically update the TODAY function in formulas

    Cheers,

    Genevieve

  • Angie F
    Angie F ✭✭
    Options

    Will this update the formula each week automatically or will I need to go in and update the formula? I'm using this in a sheet summary so I can add it as a metric on a financial dashboard. I need to make sure it updates on its own.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Angie F

    You would need to set up one of the solutions outlined in this Help Article to ensure the Today function is updated if you won't be opening the sheet every day:

    Automatically update the TODAY function in formulas

    Cheers,

    Genevieve

  • Angie F
    Angie F ✭✭
    Options

    I have tried everything you said to do and I still keep getting the unparseable error. Here is my formula: =SUMIFS([NET HOLD]1:[NET HOLD]2147,[WEEK #]1:[WEEK #]2147,@CELL=WEEKNUMBER(TODAY([Helper Date Column]1)))

    I did add the helper column and have SmartSheet updating the date like you suggested as well. I'm still missing something here. Any more ideas?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Angie F

    A few things to adjust here.

    • First of all, I would recommend removing any Row Numbers in your references, so you can reference the entire column (which will include new rows as they're added)
    • Secondly, the @cell function needs to be lowercase, or you'll receive an error.
    • Thirdly, you don't need a reference inside of the TODAY() function. You can add a number inside (these) if you want to push out the day... for example "tomorrow" would be TODAY(1).
    • The helper column updating the date is to make sure the sheet refreshes in general. It can be hidden.

    Try this:

    =SUMIFS([NET HOLD]:[NET HOLD], [WEEK #]:[WEEK #], @cell = WEEKNUMBER(TODAY()))

    Cheers,

    Genevieve

  • Angie F
    Angie F ✭✭
    Options

    I'm entering this formula in a sheet summary. It's still not working. Would I be better off trying this on a metrics sheet?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Angie F Exactly what formula are you entering and what do you mean when you say it is "not working"? Are you getting an error message or an inaccurate count?

  • Angie F
    Angie F ✭✭
    Options

    Hi Paul! I have tried every formula Genevive has given me in all of her responses. I can't get any of them to work. I'm trying to enter them in a sheet summary.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Angie F

    Can you post a screen capture showing your last try? Did you copy/paste my suggested formula or did you type it in yourself?

  • Angie F
    Angie F ✭✭
    Options

    I typed in just like you had it in your answer. It did not work. I cannot select an entire column in the sheet summary. So do I need to create a metric sheet and enter the formula there?

  • amber.lange
    amber.lange ✭✭✭✭
    Options

    @Angie F to select an entire column, even in the sheet summary, you exclude the row numbers. I've snipped two examples below to hopefully help.



  • Angie F
    Angie F ✭✭
    Options

    @amber.lange I did that for countif but it is not working for sumifs and that is the type of formula we are trying to create. @Genevieve P. could I possibly share the sheet with you and add all the stuff in the sheet summary I am needing? Or is there a time you could maybe do a quick assist with me so you can see my screen?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Angie F

    Referencing columns is the same in any function. I'd be happy to help further but we would need to see exactly what you're doing... screen captures here in the Community are the easiest so we can see your column titles and the formula you've tried, but please block sensitive data when you post. 🙂

    For example, this is the suggested formula:

    =SUMIFS([NET HOLD]:[NET HOLD], [WEEK #]:[WEEK #], @cell = WEEKNUMBER(TODAY()))

    Here's how it looks in my test sheet:

    I've highlighted the two cells it should be Summing based on the values in the formula.

    Notice that in my formula the column names light up in different colours when they're correctly referenced, and there's no error in the formula itself.

    Can you post a screen capture like this?

  • Angie F
    Angie F ✭✭
    Options

    @Genevieve P. your screenshot helped a lot! I finally got it to work. Thank you so much for your patience and all of your help! One more question if I want the formula to take the year into account how would I add that in to the formula? Right now we only have 2023 data in our sheet but plan to continue using the same sheet for 2024.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!