SUMIF and WEEKNUMBER

fred sodergren

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?

Andrée Starå

Hi Fred,

I'd recommend a different approach.

It's probably the Story Points column that needs to be converted with the VALUE function.

So replace the formula in the Value column with =VALUE([Story points]@row)

Then add another column called Weeknumber after the Value column where you'd add the week numbers 1-52 (or as needed).

In Column7 you'd add the following formula

=SUMIF([Resolution Date]:[Resolution Date], WEEKNUMBER(@cell) = Weeknumber@row, Value:Value)

Make sense?

Did that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

Andrée Starå

Excellent!

Happy to help!

Your structure should be something like this.

Value = =VALUE([Story points]@row)

Total = =SUMIF([Resolution Date]:[Resolution Date], WEEKNUMBER(@cell) =  [Week#]@row, Value:Value)

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

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

Andrée Starå

Hi Fred,

I'd recommend a different approach.

It's probably the Story Points column that needs to be converted with the VALUE function.

So replace the formula in the Value column with =VALUE([Story points]@row)

Then add another column called Weeknumber after the Value column where you'd add the week numbers 1-52 (or as needed).

In Column7 you'd add the following formula

=SUMIF([Resolution Date]:[Resolution Date], WEEKNUMBER(@cell) = Weeknumber@row, Value:Value)

Make sense?

Did that work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer! 

fred sodergren

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!

Andrée Starå

Excellent!

Happy to help!

Your structure should be something like this.

Value = =VALUE([Story points]@row)

Total = =SUMIF([Resolution Date]:[Resolution Date], WEEKNUMBER(@cell) =  [Week#]@row, Value:Value)

✅Remember! Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

fred sodergren

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.

Andrée Starå

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