Sum IF - Adding data after conditions are met

Amy Evans2

Hello,

SumIf.png

In the attached image, I want to only SUM up rows with "DONE" in the Status Column. I want to sum up by month so I want to add the values in Story Points column by month.

Example - Add up all rows with "DONE" status and total the points in January, February etc...

Thank you!

Amy

L_123

=sumifs([story points selection]:[story points selection],status:status,"Done",resolved:resolved,iferror(month(@cell),13) = 1

The above will count all values in the story points selection (i think thats the column name, youve cut the end of it off in your picture) when the status is done and the resolved column is in the month of january. Change the one at the end to reflect which month you want, and it will change the value to reflect the appropriate number.

Amy Evans2

I'm getting un UNPARSEABLE error when I have to reference another sheet for this formula. The column names in brackets [ ] reference another sheet. Can you tell me what I'm doing wrong here?

=sumifs([{Story Points}]:[{Story Points}],[{Status}]:[{Status}],"Done",[{Resolved Date}]:[{Resolved Date}],iferror(month(@cell),13) = 1

Thank you for the quick response!

Amy

Amy Evans2

Does it matter that my Date column is in MM/DD/YY format when using the "Month" reference?

L_123

=sumifs({Story Points},{Status},"Done",{Resolved Date},iferror(month(@cell),13) = 1

No hard brackets for other sheet references. Did you use the create other sheet reference functionality or did you type that in?

Amy Evans2

I used the reference link, but was trying to follow your original formula. Thank you for clarifying! This worked! Yay!

Appreciate your quick response.

L_123

np glad it worked for you.

Amy Evans2

I did a manual count and the numbers are a bit off for January. Then I used your original formula on the same sheet as the data:

=SUMIFS([Story Points Selection]:[Story Points Selection], Status:Status, "Done", Resolved:Resolved, IFERROR(MONTH(@cell), 13) = 1)

I received "#DATE EXPECTED" error.

Can you tell me if my date format is not working with this formula?

Amy

L_123

This needs to go in a text/number column, a date column expects formulas to return text or dates.

Are there multiple years in the data? that could potentially mess this formula up, and it is something we can correct for.

Amy Evans2

I figured out the issue. I have dates from 2019 mixed in and I only want to count for this year. I can fix my data source.

Thanks!

Amy

L_123

=SUMIFS([Story Points Selection]:[Story Points Selection], Status:Status, "Done", Resolved:Resolved, and(year(@cell) = year(today()),IFERROR(MONTH(@cell), 13) = 1)

this one will always correct to current year