Countif statement using two sets of criteria

Jolene Luna

I am attempting to find the number of "Tactical" cells within a specified date range. I am receiving an error UNPARSEABLE.

=COUNTIFS([Date Presented to FRB]192:[Date Presented to FRB]207, MONTH(@cell) = 4), + COUNTIFS([Force Level_FRB]:[Force Level_FRB] = "Tactical")

Paul Newcome

The error itself is caused by the comma before the + and a missing comma before the criteria in the second COUNTIFS, but to be able to count for what you describe in your post, you are going to want something more like...

=COUNTIFS([Date Presented to FRB]192:[Date Presented to FRB]207, MONTH(@cell) = 4, [Force Level_FRB]192:[Force Level_FRB]207, @cell = "Tactical")

Paul Newcome

To use dates in formulas, you have to use the DATE function. To have multiple criteria sets for the same range while only referencing the range once, you will need an AND function.

=COUNTIFS([Date Presented to FRB]179:[Date Presented to FRB]207, AND(@cell>= DATE(2021, 02, 01), @cell<= DATE(2021, 04, 15)), [Force Level_FRB]179:[Force Level_FRB]207, @cell = "TACTICAL")

Paul Newcome

The error itself is caused by the comma before the + and a missing comma before the criteria in the second COUNTIFS, but to be able to count for what you describe in your post, you are going to want something more like...

=COUNTIFS([Date Presented to FRB]192:[Date Presented to FRB]207, MONTH(@cell) = 4, [Force Level_FRB]192:[Force Level_FRB]207, @cell = "Tactical")

Jolene Luna

How would I write the formula if I am trying to using multiple dates ranging from 02/01/21 through 04/15/21 and capture all "Tactical" cases?

I am using this formula but it is not working.

=COUNTIFS([Date Presented to FRB]179:[Date Presented to FRB]207, ("@cell >=02/01/21, @cell <=04/15/21"), [Force Level_FRB]179:[Force Level_FRB]207, @cell = "TACTICAL")

The returned value is "0".

Paul Newcome

To use dates in formulas, you have to use the DATE function. To have multiple criteria sets for the same range while only referencing the range once, you will need an AND function.

=COUNTIFS([Date Presented to FRB]179:[Date Presented to FRB]207, AND(@cell>= DATE(2021, 02, 01), @cell<= DATE(2021, 04, 15)), [Force Level_FRB]179:[Force Level_FRB]207, @cell = "TACTICAL")