Formula inquiry: Counting a specific name in multiple columns in one sheet
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
-
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!
-
Thank you!! This is very helpful!
Answers
-
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!
-
=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.
-
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!
-
We must have been typing around the same time! I did not see the hidden columns, great catch!
-
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")
-
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!
-
Thank you!! This is very helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!