Help with SUMIFS formula

Paul69g

Hi All

The below formula is is returning incorrect argument and i can't figure out why

=SUMIFS([Column10]@row :[Column14]@row , [Column10]4, [Today's year]#, [Column10]3, ">=1", [Column14]3, "<=6")

I would be very grateful for your help as I appear to be missing something simple?

Find more posts tagged with

Accepted answers

Paul Newcome

@punitjha Smartsheet's SUMIFS can sum horizontally.

@Paul69g Give this a try instead:

=IF(AND([Column10]4 = [Today's year]#, [Column10]3 >= 1, [Column14]3 <= 6), SUM([Column10]@row:[Column14]@row))

If the above does not work, please provide a screenshot.

All comments

punitjha

some issue are there:

SUMIFS doesn't work like Excel's SUMIFS when using a horizontal range (like [Column10]@row :[Column14]@row ). Smartsheet’s SUMIFS only supports vertical column ranges.
[Column10]4, [Column10]3, etc., are individual cells, not column ranges.
[Today's year]# is unclear — # is not valid in this context unless it refers to a cell reference or cross-sheet reference.

Paul69g

Hi Punitjha

If i cant use SUMIFS, how do i sum a horizontal range then?

I need to sum a row (6 cells/months) if the year is today's year (link from the sheet summary) and the months are between 1 & 6

Paul Newcome

@punitjha Smartsheet's SUMIFS can sum horizontally.

@Paul69g Give this a try instead:

=IF(AND([Column10]4 = [Today's year]#, [Column10]3 >= 1, [Column14]3 <= 6), SUM([Column10]@row:[Column14]@row))

If the above does not work, please provide a screenshot.

punitjha

right. it can be done using below formula too

=SUMIFS([Column10]@row :[Column14]@row , [Column10]4:[Column14]4, [Today's year]@row , [Column10]3:[Column14]3, ">=1", [Column10]3:[Column14]3, "<=6")

Paul Newcome

@punitjha That still will not work for a few reasons.

Paul69g

Hi Paul

Your solution is working perfectly, thank you.

Cheers

Paul

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.