SUMIFS help in summary panel

System
System Employee
in Formulas and Functions
This discussion was created from comments split from: Count If Current Week.
· Share on FacebookShare on Twitter
«1

Answers

  • Angie F
    Angie F ✭✭

    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?


    image.png


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    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?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    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?

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    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.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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?

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    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?

    · Share on FacebookShare on Twitter
  • amber.lange
    amber.lange ✭✭✭✭

    @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.


    image.png
    image.png


    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    @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?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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:

    Screenshot 2023-11-17 at 13.30.39.png

    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?

    · Share on FacebookShare on Twitter
  • Angie F
    Angie F ✭✭

    @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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!