Can I use a CountIf(And) statement?

Lori Leighton

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

Jeff Reisman

@Lori Leighton

We have the COUNTIFS function for when we want to consider more than one criteria. AND is built-in 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.)

Lori Leighton

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

Jeff Reisman

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