# SUMIFS and CONTAINS or MAX

Options
✭✭✭
edited 06/08/23

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?

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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!

• ✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

This help article will explain it in detail:

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!