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

jjluna

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!

Jeff Reisman

@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")

jjluna

Thank you!! This is very helpful!

Jeff Reisman

@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.

Dan W

=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

https://community.smartsheet.com/discussion/comment/330513#Comment_330513

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].

Dan W

https://community.smartsheet.com/discussion/comment/330516#Comment_330516

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

jjluna

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

@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")

jjluna

Thank you!! This is very helpful!