Countifs with reference sheet and current sheet criteria
I am trying to create a count if depending on both columns from my main sheet (utilizing reference sheets) and columns in my current sheet. I tried the following formula but I get an incorrect argument set error. The 2 OPS Tracking Range columns are checkboxes that I want to only count if they are not checked.
=COUNTIFS({OPS Tracking Range 2}, 0, {OPS Tracking Range 3}, 0, [Start Month]:[Start Month], [Date Started]@row)
What am I doing wrong?
Comments
-
Try referencing the month of the Start Date as your criteria.
=COUNTIFS({OPS Tracking Range 2}, 0, {OPS Tracking Range 3}, 0, [Start Month]:[Start Month], MONTH([Date Started]@row))
-
The Date Started column includes month and year. I have attached a screenshot.
-
Ah. Ok. I am going to suggest a helper column that will look at the Month/Year and designate a number for the month (1 - 12 for simplicity). You can then reference that helper column in your formula.
-
How would that work if I have May 2018 and then further down, May 2019?
-
You would have a few options. The easiest would be another helper column to pull the year and include that as part of your criteria in your COUNTIFS.
-
Thank you. What would that look like?
Originally I had used the cell linking to pull in the parent check and done columns and the formula
=COUNTIFS([Parent Check]:[Parent Check], 0, Done:Done, 0, [Start Month]:[Start Month], [Date Started]@row) worked fine.
But when I tried to change the Parent Check to OPS Tracking Range 1 and Done to OPS Tracking Range 2 (columns from reference sheet) instead, I get the incorrect argument set error.
=COUNTIFS({OPS Tracking Range 1}, 0, {OPS Tracking Range 2}, 0, [Start Month]:[Start Month], [Date Started]@row)
Why would the same formula not work when I reference the other sheet versus a column within the same sheet?
-
I don't know why I didn't catch this before... You have two ranges referencing one sheet and a third range referencing a different sheet. All ranges have to be on the same sheet.
-
Ugh, okay. I was trying to save myself from having to update the cell linking in these 2 columns (we add rows in the middle of our main sheet) but I guess I will have to continue to do that instead. Thank you for your help though.
-
What cells are linked? Are you able to provide some screenshots of both sheets and a breakdown of exactly what you are trying to do?
-
Sure. OPS Tracking is the main sheet. I'm using the OPS Status for Reporting as my sheet with my formulas to use in my Dashboard.
In the main sheet, I have the start date for each row. However, I only want to count the children tasks so I have a parent check column in order to remove the parent tasks from the count. I also have a Done column because I do not want to count the tasks that are already complete. I also want my count to be done by month so I have a formula to convert the mm/dd/yyyy in my start date column to Month Year. What I am trying to do is reference the parent check and done columns directly from the main sheet so I have 2 fewer columns to relink using cell linking (we add children task rows to the parent tasks throughout the sheet) but I also have to use the Start Month in this sheet.
If I keep it all in one sheet using cell linking, I am able to use the formula:
=COUNTIFS([Parent Check]:[Parent Check], 0, Done:Done, 0, [Start Month]:[Start Month], [Date Started]@row)
However, when I try to reference Parent Check and Done from my main sheet with this formula:
=COUNTIFS({OPS Tracking Range 1}, 0, {OPS Tracking Range 2}, 0, [Start Month]:[Start Month], [Date Started]@row)
I get an #INCORRECT ARGUMENT SET error.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!