Can I use a CountIf(And) statement?
I want to calculate if a department has given 2 weeks lead time on their request. I have a sheet with over 1,000 rows. One Column is 'Department' and the other column is "2 weeks given".
I wrote and countif(and) statement but it is telling me #incorrect argument. I am calculating this in a separate sheet so I can do a bar graph in a Dashboard.
=COUNTIF(AND({COMPLETED FY Independent Contracts Range 2} = "Office of the President", {COMPLETED FY Independent Contracts Range 4} = "Yes"))
Note: Range 2 is my Department Column, Range 4 is my 2 weeks given column
Any help is greatly appreciated.
Lori
Answers

We have the COUNTIFS function for when we want to consider more than one criteria. AND is builtin to this function, so that the criteria you list must all be true for the row to be counted. (Same goes for SUMIFS.)
Additionally, the syntax is: =COUNTIFS(Criteria Range 1, Criteria, Criteria Range 2, Criteria 2...) where your range and criteria are separated by a comma.
=COUNTIFS({COMPLETED FY Independent Contracts Range 2}, "Office of the President", {COMPLETED FY Independent Contracts Range 4}, "Yes")
(See the links in my signature and bookmark them. They are very helpful when writing and troubleshooting formulas.)
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!

@Jeff Reisman Thanks, I had previously looked at those. Unfortunately, I cannot tell from those sheets where I am going wrong with the formula.

@Lori Leighton This should work:
=COUNTIFS({COMPLETED FY Independent Contracts Range 2}, "Office of the President", {COMPLETED FY Independent Contracts Range 4}, "Yes")
If you still get the #Incorrect Argument Set error, double check your ranges. Make sure they are both the same size. In other words, if you selected the entire Department column for Range 2, make sure that Range 4 selects the entire 2 Weeks Given column as well (and vice versa.)
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!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 443 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!