SUMIFS and CONTAINS or MAX

Nicole V

Here's a fun one that's got me scratching my head. So I am trying to add the rows that contain the terms Agile Kanban and waterfall.

SUM works,

=SUMIF((Variable$2:Variable$9), HAS((Variable$2:Variable$9), (Variable@row)), (A$2:A$9))

returns a 0

and

=SUMIF(Variable$2:Variable$9, CONTAINS(Variable@row, Variable$2:Variable$9), A$2:A$9)

also returns a zero.

The variable column contains text and Column A is a Countif formula to my master data sheet.

=COUNTIF({MstrPrj_PrType}, =$Variable@row).

Here's my dilemma - I have read that the max number of cross-sheet references we can use in a sheet is 100. so I am assuming that each cross-sheet reference to a column counts as a reference, hence why I am trying to use my calc to ref the sheet only.

Any Ideas?

ker9

Hi @Nicole V

Please try this - presuming you are inserting the formula on rows 13-15 in the Total column of your screenshot:

=SUMIFS(Total$2:Total$9, Variable$2:Variable$9, CONTAINS(Variable@row, @cell))

Hope this helps!

ker9

Hi @Nicole V

Please try this - presuming you are inserting the formula on rows 13-15 in the Total column of your screenshot:

=SUMIFS(Total$2:Total$9, Variable$2:Variable$9, CONTAINS(Variable@row, @cell))

Hope this helps!

Nicole V

You are an angel! Thank you thank you thank you! It worked.

Nicole V

Ker - could you please explain the formula to me - I am wondering what @cell means?

ker9

@Nicole V

This help article will explain it in detail:

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell