COUNTIFS with multiple criteria

I need help with creating a formula where there are several criteria based off two different things. My columns are "Received from LO" needs to be yes and "Document Type" is several criteria "Lease"; "Easement"; "Option"; and "Easement Option". I can't seem to figure it out. I have the first one done, but can't figure out how to get the multiple criteria in one formula.

=COUNTIFS([Document Type]:[Document Type], ="Lease", [Received from Counterparty]:[Received from Counterparty], ="Yes")

Any help would be appreciated.

Thanks, Tina

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Tina Chamblee

    Try this:

    =COUNTIFS([Document Type]:[Document Type], OR(@cell = "Lease", @cell = "Easement", @cell = "Option", @cell ="Easement Option"), [Received from Counterparty]:[Received from Counterparty], "Yes")

    If this hasn't worked, it would be helpful to see a screen capture of your sheet showing the Document Type column, but please block out sensitive data. (For example, if this is a multi-select column we'd actually need to use the HAS function as well).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Hayley B
    Hayley B ✭✭✭

    Hi Tina!

    In your argument above, it shows [Document Type]:[Document Type], ="Lease".

    Having , ="Lease" is being read as wrong because the comma and equal sign essentially serve the same purpose here. Try this. 😊

    =COUNTIFS([Document Type]:[Document Type], "Lease", [Received from Counterparty]:[Received from Counterparty], "Yes")

  • I also need for it to count where Document Type is "Easement" and "Option" and "Easement Option" where Received from Counterparty is Yes. Do you know how to do that?

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Tina Chamblee

    Try this:

    =COUNTIFS([Document Type]:[Document Type], OR(@cell = "Lease", @cell = "Easement", @cell = "Option", @cell ="Easement Option"), [Received from Counterparty]:[Received from Counterparty], "Yes")

    If this hasn't worked, it would be helpful to see a screen capture of your sheet showing the Document Type column, but please block out sensitive data. (For example, if this is a multi-select column we'd actually need to use the HAS function as well).

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!