Help on fiormula: countifs

Grace · 08/07/18

Hi all, please need your advice for the correct formula for getting the count of projects that are active within a specific month? We would like to track the number of projects that are still active for 2018.

I'm trying the formula below but I'm getting an #INCORRECT ARGUMENT error.

=COUNTIFS(Stage:Stage, [Forecasted End Date]:[Forecasted End Date], IFERROR(AND(MONTH(@cell) <= 1, YEAR(@cell) = 2018), Status:Status, "active"))

The Stage headers that are active are called:

Planning

Strategy

Tender

Evaluation

Contract

Thanks in advance for the response.

Kind regards,

Grace

J. Craig Williams · 08/08/18

I would suggest rethinking your formula.

1. There 5 Stages that are considered active. Perhaps there are fewer that are not considered active?

2. What about a project that is active in 2018 but ends in January 2019?

3. I would put this formula into a separate column (check box). It can be incorporated directly into the COUNTIFS, but it isn't pretty

=IF(OR(AND(Start@row < DATE(2018, 1, 1), Finish@row < DATE(2018, 1, 1)), AND(Start@row > DATE(2018, 12, 31), Finish@row > DATE(2018, 12, 31))), 0, 1)

This will be checked if the Start or Finish date occurs in 2018.

4. For the Status:Status check, use OR("Planning", "Strategy", etc...) or flip it based on #1 above to NOT(OR("Completed", etc...)

I hope that gets you started.

Craig

Help on fiormula: countifs

Comments

Help Article Resources

Categories