Referencing multiple sheets in 1 cell

Hi

I am trying to make a metric to show on our dashboard how many Initiated and Overdue project statuses we have. I have created a metrics sheet for this.

I am trying to count how many times Initiated appears in a status column from 3 sheets. Error is showing "incorrect argument set". What am i missing?

Thank you


Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 12/19/23 Answer ✓

    Your first example is the closest but you are missing a few ) and then have two where they should not be. I've tried to highlight in bold here. If you edit the formula in smartsheet the parentheses are color coded so you can see the pairs and where there are spares.

    Each COUNTIF should have a ( then the range then a comma, then criteria, then )

    =COUNTIF({Connection Agreements Range 1}, [Primary Column]12) + COUNTIF({Licences / Permits Status}, [Primary Column]12) + COUNTIF({PPA Compliance Register status}, [Primary Column]12) + COUNTIF({Rules Range 1}, [Primary Column]12)

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Happy to help!

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Hey there @Laura G

    The COUNTIFS function is looking for a row where all the criteria are met. So the three columns need to be on the same sheet. If you want to COUNT when Col 1 in sheet 1 = Initiated and Col 1 in sheet 2 = Initiated and Col1 in sheet 3 = Initiated then you could use COUNTIF and add the three COUNTIFs together.

    =COUNTIF({Licences / Permit Status},[Primary Column]@row)+COUNTIF({PPA Compliance...},[Primary Column]@row)+COUNTIF({Rules Range 1},[Primary Column]@row)

  • Laura G
    Laura G ✭✭✭✭

    Hi KPH

    I have tried these 2 scenarios but it didn't work for me. Do you know what I am missing? Thank you

    =COUNTIF({Connection Agreements Range 1}, [Primary Column]12 + COUNTIF({Licences / Permits Status}), [Primary Column]12 + COUNTIF({PPA Compliance Register status}, [Primary Column]12 + COUNTIF({Rules Range 1}), [Primary Column]12))

    =COUNTIF({Connection Agreements Range 1}, [Primary Column]12, COUNTIF({Licences / Permits Status}), [Primary Column]12, COUNTIF({PPA Compliance Register status}, [Primary Column]12, COUNTIF({Rules Range 1}), [Primary Column]12))

  • KPH
    KPH ✭✭✭✭✭✭
    edited 12/19/23 Answer ✓

    Your first example is the closest but you are missing a few ) and then have two where they should not be. I've tried to highlight in bold here. If you edit the formula in smartsheet the parentheses are color coded so you can see the pairs and where there are spares.

    Each COUNTIF should have a ( then the range then a comma, then criteria, then )

    =COUNTIF({Connection Agreements Range 1}, [Primary Column]12) + COUNTIF({Licences / Permits Status}, [Primary Column]12) + COUNTIF({PPA Compliance Register status}, [Primary Column]12) + COUNTIF({Rules Range 1}, [Primary Column]12)

  • Laura G
    Laura G ✭✭✭✭

    Thank you so much! That worked!

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Happy to help!