SUMIFS and CONTAINS or MAX

Options
Nicole V
Nicole V ✭✭✭
edited 06/08/23 in Formulas and Functions

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?

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓
    Options

    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!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!