COUNTIFS formula help please

Jeana

Can't figure out why this isn't working. I get #Incorrect Arguement

=COUNTIFS([1 Weeks Tasks]$2:[8 Weeks Tasks]$100, =1, [Task Owner]:[Task Owner], ="Copyedit")

I'm trying to get a count of how many '1''s in the designated row/columns (1 Weeks Tasks:8 Weeks Tasks) AND if Task Owner is Copyedit. The trick , I think, is that I have a sum in row 1 of this sheet so I don't want it to look at that row. That's why the absolute values on the first range. I do know that the formula works fine like this.

=COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")

I want to count ALL 8 weeks of Tasks that have "Copyedit" as the Task Owner - and again, the first row is a sum field so I don't want to include that, in case there a sum of "1" it would throw the total off.

Any ideas?

Thanks so much!

Jeana

John Jonassen

Hi @Jeana

You will need to do an individual countifs for each of the X Weeks Tasks Columns.

=COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")+COUNTIFS([2 Weeks Tasks]:[2 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")+COUNTIFS([3 Weeks Tasks]:[3 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")...so on and so forth until you have captured all 8 of your columns.

I'm learning that Smartsheet has different languages for formulas based on different regions a user might be located it. So, I'm suspicious of the =1 and ="Copyedit", but if it works in the 1 Weeks Tasks, then the = should stay.

John Jonassen

Hi @Jeana

You will need to do an individual countifs for each of the X Weeks Tasks Columns.

=COUNTIFS([1 Weeks Tasks]:[1 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")+COUNTIFS([2 Weeks Tasks]:[2 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")+COUNTIFS([3 Weeks Tasks]:[3 Weeks Tasks], =1, [Task Owner]:[Task Owner], ="Copyedit")...so on and so forth until you have captured all 8 of your columns.

I'm learning that Smartsheet has different languages for formulas based on different regions a user might be located it. So, I'm suspicious of the =1 and ="Copyedit", but if it works in the 1 Weeks Tasks, then the = should stay.

Jeana

I see your point and I appreciate the quick response. I'll test this out. I'm still not sure how to ignore the first row though.

Jeana

John Jonassen

Hi @Jeana

Does the first row that you are wanting to ignore contain "Copyedit" in the Task Owner column? If not, then it's already being disqualified/removed from your collection of Countifs formulas.