Help w/COUNTIFS and CONTAINS

jmo
jmo ✭✭✭✭✭✭

Hi - I know this has been asked several times but I cannot seem to get the syntax right on my summary formula.

I need to count the total number of agreements that are in the DIVISION REQUESTED BY column with any part of General Counsel there

as well as any of those entries that have INCORRECT FORMAT checked.

I tried:

=COUNTIFS([Division requested by]:[Division requested by], (CONTAINS("general counsel", @cell, [Incorrect format]:[Incorrect format], 1)))

But get the #INCORRECT ARGUMENT SET error.

Any help would be greatly appreciated.

Thanks.

Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    You could just make and hide a dummy column with your formula

    =IF(CONTAINS("GENERAL COUNSEL", [Division requested by]@row), 1, 0)

    Then use that newly created column for your COUNT or SUM.

    =SUM([New Column]:[New Column])

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

  • jmo
    jmo ✭✭✭✭✭✭

    Thanks for the tip but i reused another formula that seemed to work:

    =COUNT(COLLECT([Agreement Author]:[Agreement Author], [Main row]:[Main row], 1, [Dropped off report]:[Dropped off report], 0, [Approved thru Salesforce]:[Approved thru Salesforce], 0, [Incorrect format]:[Incorrect format], 1, [Division requested by]:[Division requested by], (CONTAINS("GENERAL COUNSEL", @cell))))

    I had multiple columns that needed to be either true or false and then CONTAINS "general counsel".

    That seemed to work just fine.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!