SUMIFS and CONTAINS or MAX
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 crosssheet references we can use in a sheet is 100. so I am assuming that each crosssheet 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

Hi @Nicole V
Please try this  presuming you are inserting the formula on rows 1315 in the Total column of your screenshot:
=SUMIFS(Total$2:Total$9, Variable$2:Variable$9, CONTAINS(Variable@row, @cell))
Hope this helps!
Answers

Hi @Nicole V
Please try this  presuming you are inserting the formula on rows 1315 in the Total column of your screenshot:
=SUMIFS(Total$2:Total$9, Variable$2:Variable$9, CONTAINS(Variable@row, @cell))
Hope this helps!

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

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

Help Article Resources
Categories
Check out the Formula Handbook template!