SUMIF FORMULA HELP

Ed Gadd · 12/18/23

I have the formula

=SUM({Tracker COST BENEFIT})

This totals the whole column of another sheet, I'd like to filter that total by counting only the items which are closed.

I've tried

=SUMIFS({Tracker COST BENEFIT}, ({Tracker STATUS}, "closed"))

But this doesn't seem to work Anyone have any ideas?

Stefan · 12/18/23

Hi @Ed Gadd,

ups, sorry, obviously I should try to read before writing ;-)

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

Too many brackets.

Hope now this helps

Stefan

SteyJ · 12/18/23

https://community.smartsheet.com/discussion/comment/410031#Comment_410031

Does this work?

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

Stefan · 12/18/23

Hi @Ed Gadd,

I think the logical condition is not there ;-)

=SUMIFS({Tracker COST BENEFIT}, ({Tracker STATUS}, ="closed"))

Hope this helps

Stefan

Ed Gadd · 12/18/23

Hi @Stefan,

Thanks for the quick reply. Just tried you suggestion. I get the #unparseable as a response.

Any thoughts?

Many thanks

Ed

Stefan · 12/18/23

Hi @Ed Gadd,

ups, sorry, obviously I should try to read before writing ;-)

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

Too many brackets.

Hope now this helps

Stefan

SteyJ · 12/18/23

https://community.smartsheet.com/discussion/comment/410031#Comment_410031

Does this work?

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed")

Ed Gadd · 12/18/23

Brilliant, thanks all for the help

SteyJ · 12/18/23

https://community.smartsheet.com/discussion/comment/410040#Comment_410040

Anytime 👍

Stefan · 12/18/23

@Ed Gadd,

happy to help :-)

Stefan

Ed Gadd · 12/18/23

Just in interest of learning. If I now wished to calculate the cost benefit of closed items per month would this be the correct approach?

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

I got the result incorrect argument.

SteyJ · 12/18/23

https://community.smartsheet.com/discussion/comment/410044#Comment_410044

I don’t think you need the and functions, instead just separate them with commas. The reason why you are getting incorrect argument is because you aren’t giving the ifs statement a date column to check. The formula for checking date appears to be correct though

im assuming you would like to check the data for January of 2023?

Ed Gadd · 12/18/23

Hi @SteyJ ,

Thanks for your help

Yes for this example I'd like to check January 2023

I've removed the AND

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", (IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

Still not quite working #unparseable. I feel its just a comma or bracket in the wrong place?

SteyJ · 12/18/23

https://community.smartsheet.com/discussion/comment/410047#Comment_410047

After “closed”, add the {date column} use the same sheet reference for checking the year.

sumifs takes a range then criteria, so you just need to add the criteria for date

it would look like this

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed",
{Date}, IFERROR(MONTH(@cell), 0) = 1, {Date},  IFERROR(YEAR(@cell), 0) = 2023)

Ed Gadd · 12/18/23

@SteyJ

Thanks for all the support today. I reference the date column but still no success. Any thoughts?

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed", {Tracker ECD}, (IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

SteyJ · 12/18/23

Make sure you are enclosing your IFERROR properly. Try this:

=SUMIFS({Tracker COST BENEFIT}, {Tracker STATUS}, "closed",
{Tracker ECD}, IFERROR(MONTH(@cell), 0) = 1, {Tracker ECD},  IFERROR(YEAR(@cell), 0) = 2023)

Ed Gadd · 12/18/23

@SteyJ ,

Amazing, thankyou so much. Works brilliantly. Great education this afternoon too. Many many thanks

Ed

SUMIF FORMULA HELP

Best Answers

Sincerely,

Answers

Sincerely,

Sincerely,

Sincerely,

Sincerely,

Sincerely,

Help Article Resources

Categories