SUMIFS formula with 1 Vertical and 2 Horizontal Criteria & Crosssheet references

Michelle Choate 2

Smartsheet Community - I AM STUMPED!! I am trying to do a crosssheet SUMIFS formula with 1 Column and 2 Rows of Data.

I need to sum across {Weeks 1 to 104}

Column to match : {Helper Column} = Baseline@row

Vertical Rows {Month} needs to match Month@row, and Row {Year} needs to match Year@row

I have tried SUMIFS, JOIN, COLLECT, INDEX, IF, SUM…. Any guidance?

@Paul Newcome @Darren Mullen ??

Find more posts tagged with

Accepted answers

Michelle Choate 2

Unfortunately yes - that is the rub. And I can't do that sadly because the whole idea is to get the calculations out of that sheet and you never know how many weeks are going to be included in what month. So what I ended up doing was scrubbing the year altogether from the requirements and doing a SUM across only the first 52 weeks for the first 12 months, then across the second batch of 52 weeks for the next twelve months. So the first formula became:

SUM(INDEX({Week 1 to 52}, MATCH(Baseline@row , {Helper Column}, 0), MATCH(Month@row , {Month of Weeks 1 to 52}, 0)))

And then the same for the second batch just:

SUM(INDEX({Week 53 to 104}, MATCH(Baseline@row , {Helper Column}, 0), MATCH(Month@row , {Month of Weeks 53 to 104}, 0)))

All comments

Mark Rojas

@Michelle Choate 2

Since I cant see exactly what you are trying here is my first pass at it. Try:

=SUMIFS({Weeks 1 to 104}, {Helper Column}, @cell = Baseline@row, {Month}, @row = Month@row,, {Year}, @row = Year@row)

If this doesn't work it might be a @cell for the last two arguments. (This would be dependent on how your data is structured)

~MR

Michelle Choate 2

I tried that and it did not work with either the at cell or at row. That was the very first thing I tried!!

=SUMIFS({Week 1 to 104}, {Helper Column}, Baseline@row , {Month}, @cell = Month@row , {Year}, @cell = Year@row )

Michelle Choate 2

@Paul Newcome @Darren Mullen HELP!!

Paul Newcome

Are you able to provide screenshots for context?

Genevieve P.

Hi @Michelle Choate 2

When you say {Weeks 1 to 104}, are these 104 columns in one range?

If so, I would use a helper formula in the source sheet to sum across all those columns first. Then you can use that single column in your cross-sheet formula, does that make sense?

Cheers,
Genevieve

Michelle Choate 2

SUM(INDEX({Week 1 to 52}, MATCH(Baseline@row , {Helper Column}, 0), MATCH(Month@row , {Month of Weeks 1 to 52}, 0)))

And then the same for the second batch just:

SUM(INDEX({Week 53 to 104}, MATCH(Baseline@row , {Helper Column}, 0), MATCH(Month@row , {Month of Weeks 53 to 104}, 0)))

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.