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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!