Sheet Summary Formula Help


I am trying to summarise a contact list column, however the problem is that I have a mixture of name and email address (a common problem with this property type it seems).

To ensure that I have captured both occurrences, I have tried the formula below, but I get #invalid data type.

Is someone able to advise where I am going wrong?

=COUNTIFS(OR(Responsible:Responsible, "Natalie Gorman", Responsible:Responsible, "[email protected]"), Status:Status, <>"Blue")

Many thanks

Natalie Gorman


  • Kaveri Vipat
    Kaveri Vipat ✭✭✭✭✭✭

    Hi Natalie Gorman, 

    You can try this formula- I have created a helper column 'H1'. So, if there is the name or email address of "Natalie" and the status is not blue then it will show "1" and then I have applied a formula in the sheet summary to count the 'H1'. This is how we will get the required count. 

    H1 column formula is: =IF(AND(Responsible@row = "Natalie Gorman", Status@row <> "Blue"), 1, IF(AND(Responsible@row = "[email protected]", Status@row <> "Blue"), 1, "")) 

    Sheet summary formula is : =COUNT([H1]:[H1]) 


    Kaveri Vipat

    Senior Associate - Smartsheet Development, Ignatiuz Software

    2023 Core Product Certified

    Did this answer help you? Show some love by marking this answer as "Insightful💡" or "Awesome❤️" and "Vote Up⬆️"

  • Natalie Gorman
    Natalie Gorman ✭✭✭✭

    Hi Kaveri

    Thank you for your response.

    Unfortunately I don't think this will work for me as I have a number of people I am looking to summarise for in the sheet summary tool.

    Kind regards,

    Natalie Gorman

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!