# SUMIF and WEEKNUMBER

I have a connector that pulls in all tickets closed after 1/1/2020, and the points associated with each. I want to sum up the points for all tickets closed each week.

My formula is:

=SUMIF(Week:Week, 1, [Story Points]:[Story Points])

I did some searching and found you can't sum on formulas, and added the 'Value' column

=VALUE(Week@row)

I still get 'Invalid Data Type'. How do I achieve this without manually writing out a year's worth of dates?

Tags:

• ✭✭✭✭✭✭

Yes, as long as the formula structure is intact on the previous two cells in your case, it should work perfectly.

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Andrée, yes thank you, that does work!

I have two follow up questions.

First, the goal is to sum up the points for one of the 52 weeks in the year. I want one number per week. Currently Column7 is summing the week number adjacent to it, showing the same info multiple times. (Lots of week 1!) How do we limit it to one calc per week?

Second, this sheet will add infinite rows over the year. Is there a way to autofill these formulas down the columns? Better yet, do you have an example of how I should have set this sheet up?

Thank you!

• That's great! From this post, it looks like the 'value' formula is automatically carried down the 'value' column? If so, I can forget about the sheet and start creating reports on it's data.

• ✭✭✭✭✭✭

Yes, as long as the formula structure is intact on the previous two cells in your case, it should work perfectly.

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!