Count if formula help

Options

I am trying to count the total of partially approved awards from an separate nomination process we have running on an alternative sheet. I am using sheet referencing to pick up the data from certain columns.

My formula is

=COUNTIF({EMEA Local - Line Manager Approval} = "Approve", {EMEA Local - P&L Approval Response} = ""),1, "")

I am getting the below error message

Can anyone provide some guidance of where i am going wrong here?

Many thanks

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    Hi @GeorgiaL,

    You want to use COUNTIFS here:

    =COUNTIFS({EMEA Local - Line Manager Approval} = "Approve", {EMEA Local - P&L Approval Response} = "")

    This should count the rows that have Approve in the Line Manager approval and are blank in P&L Approval Response.

    Hope this helps - if I've misunderstood something or you've any problems/questions then just post! 🙂

  • GeorgiaL
    GeorgiaL ✭✭✭
    Options

    Hi Nick

    Thanks for your response. I have tried this and now got the error code #INVALID OPERATION

    do you know what might be causing this issue?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @GeorgiaL

    Just jumping in here to clarify syntax! With a COUNTIFs, you'll want to use a comma between the {range} and your criteria, like so:

    =COUNTIFS({Range 1}, "Criteria 1", {Range 2}, "Criteria 2")

    Or in your case, something like this:

    =COUNTIFS({EMEA Local - Line Manager Approval}, "Approve", {EMEA Local - P&L Approval Response}, "")

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!