Formula inquiry: Counting a specific name in multiple columns in one sheet

Options

I am trying to create a formula within Sheet Summary to count the number of times each initiative was listed within a cell in four different columns. The columns are below. I used the following formula: =COUNTIF([Initiative]:[Initiative], [Initiative 2]:[Initiative 2], [Initiative 3]:[Initiative 3], [Initiative 4]:[Initiative 4], "New Patient Scheduling Lag") but it returned an "incorrect argument statement". Can you please help me with the correct formula? Thank you!



Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @jjluna

    In that case, specify the row numbers as part of each range. Keep in mind, the ranges MUST MATCH. So if you indicate rows 42-73 as the range for the Initiative column, you need to indicate rows 42-73 for the [Initiative 2] column, and so on. Below, I used that those rows 42-73 to specify the row selection for the formula:

    =COUNTIF([Initiative]42:[Initiative]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 2]42:[Initiative 2]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 3]42:[Initiative 3]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 4]42:[Initiative 4]73, "New Patient Scheduling Lag")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • jjluna
    jjluna ✭✭
    Answer ✓
    Options

    Thank you!! This is very helpful!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @jjluna

    There are two ways to do this:

    You can break these into individual formulas with results added together. This will count all instances of the value, even if it appears twice in the same row:

    =COUNTIF([Initiative]:[Initiative], "New Patient Scheduling Lag") + COUNTIF([Initiative 2]:[Initiative 2], "New Patient Scheduling Lag") + COUNTIF([Initiative 3]:[Initiative 3], "New Patient Scheduling Lag") + COUNTIF([Initiative 4]:[Initiative 4], "New Patient Scheduling Lag")

    Or you can use COUNTIFS instead of COUNTIF, and include the OR function. This will count every row where the value appears, but if the value is in two columns on the same row, it will only count that as one occurrence of the value:

    =COUNTIFS(OR([Initiative]:[Initiative], "New Patient Scheduling Lag"[Initiative 2]:[Initiative 2], "New Patient Scheduling Lag", [Initiative 3]:[Initiative 3], "New Patient Scheduling Lag", [Initiative 4]:[Initiative 4], "New Patient Scheduling Lag"))

    Either way you have to include the criteria separately with each range.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Dan W
    Dan W ✭✭✭✭✭
    edited 05/20/22
    Options

    =COUNTIFS(Initiative:[Initiative 4], Initiative:[Initiative 4] = "New Patient Scheduling Lag")

    The first reference is where you are searching, the second is what needs to have the "Words".

    So this formula reads look in this box, if the box has "Words" then count them.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    That was my initial thought as well, however you may notice from the screenshot that the sheet has some hidden columns between the Initiative columns, and we don't know what's in those columns or how that would affect a range of [Initiative]:[Initiative 4].


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Dan W
    Dan W ✭✭✭✭✭
    Options

    We must have been typing around the same time! I did not see the hidden columns, great catch!

  • jjluna
    jjluna ✭✭
    Options

    Thank you all for your responses. I used the COUNTIF formula as it works best for this sheet. The hidden columns are metrics as a number or percent so the formula options you provided will work; however, I will eventually need to write the formula for a range of rows as this sheet is going to be used to update leadership on a monthly basis one month at a time but all months will be maintained on the sheet. Can you help with specifying a row range using the same formula?

    =COUNTIF([Initiative]:[Initiative], "New Patient Scheduling Lag") + COUNTIF([Initiative 2]:[Initiative 2], "New Patient Scheduling Lag") + COUNTIF([Initiative 3]:[Initiative 3], "New Patient Scheduling Lag") + COUNTIF([Initiative 4]:[Initiative 4], "New Patient Scheduling Lag")

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @jjluna

    In that case, specify the row numbers as part of each range. Keep in mind, the ranges MUST MATCH. So if you indicate rows 42-73 as the range for the Initiative column, you need to indicate rows 42-73 for the [Initiative 2] column, and so on. Below, I used that those rows 42-73 to specify the row selection for the formula:

    =COUNTIF([Initiative]42:[Initiative]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 2]42:[Initiative 2]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 3]42:[Initiative 3]73, "New Patient Scheduling Lag") + COUNTIF([Initiative 4]42:[Initiative 4]73, "New Patient Scheduling Lag")

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • jjluna
    jjluna ✭✭
    Answer ✓
    Options

    Thank you!! This is very helpful!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!