How to combine a COUNTIF and a COUNTIFS formula

I need to get a number of how many contracts were approved, not approved, pending in April, May, June, etc. of the same year. The following formulas work separately to give me:

  • The count of contracts approved =COUNTIF(AND([Status]:[Status], "Approved")
  • The count of April dates in column =COUNTIFS([Deadline]:[Deadline], IFERROR(MONTH(@cell), 0) = 4)

However, I need to combine them to show count of contracts approved in April, etc. When I have tried to combine them, I get an unparseable message. As always, thanks for the amazing help from this forum!


 

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Profe17

    Hope you are fine, please try the following

    =COUNTIFS([Deadline]:[Deadline], IFERROR(MONTH(@cell), 0) = 4,[Status]:[Status], "Approved")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    Hi @Profe17

    Hope you are fine, please try the following

    =COUNTIFS([Deadline]:[Deadline], IFERROR(MONTH(@cell), 0) = 4,[Status]:[Status], "Approved")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Works perfectly! Thanks!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 07/01/21

    @Profe17

    You are welcome and I will be happy to help you any time.Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • hello @Bassam Khalil , could you help me with a similar scenario? I attempting to gather if the specific store is checked in two different sheets. I have tried several iterations of this formula and none seem to work. Can you help me?

    =IF(COUNTIFS({checked2023}, 1, {storenu2023}, [Store #]@row, {checked2022}, 1, {storenu2022}, [Store #]@row)) > 0, "Yes", "No"

    =IF(COUNTIFS({checked2023}, 1, {storenu2023}, [Store #]@row), >0, "Yes", "No" + COUNTIFS({checked2022}, 1, {storenu2022}, [Store #]@row) > 0, "Yes", "No")

    A note, when I use the bolded on its own, it works. It returns the Yes or No from the one sheet but when I try to combine both results from two sheets, it does not work.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!