Countifs with reference sheet and current sheet criteria

Heather K

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?

Paul Newcome

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

Heather K

The Date Started column includes month and year. I have attached a screenshot.

 

Paul Newcome

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.

Heather K

How would that work if I have May 2018 and then further down, May 2019? 

Paul Newcome

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.

Heather K

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?

Paul Newcome

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.

Heather K

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.

Paul Newcome

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?

Heather K

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.