SUMIFS help in summary panel

System · 11/17/23

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

Angie F · 11/12/23

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. · 11/13/23

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 · 11/13/23

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. · 11/13/23

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 · 11/15/23

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. · 11/15/23

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 · 11/15/23

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 · 11/15/23

@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 · 11/16/23

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. · 11/16/23

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 · 11/16/23

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 · 11/16/23

@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 · 11/17/23

@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. · 11/17/23

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 · 11/17/23

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

SUMIFS help in summary panel

Answers

Help Article Resources

Categories