Countifs formula help

James Brett

Hi There,

I've been looking all afternoon to see how I can complete my countifs formula and I think I'm close.

I need to count the number of rows between a date range based on if another column states a certain value. So in the case below, if the date is between X and Y in the 'date of incident' column and if the department involved is selected as fabrication.

So far i have

=COUNTIFS([Date of Incident]:[Date of Incident], AND((@cell >= DATE(2023, 8, 1), @cell <= DATE(2023, 8, 31))), AND([department

its the last part i cant get working, where it will count the rows if the department involved is filtered to fabrication.

Any help would be greatly appreciated.

Jason Albrecht

Hi @James Brett

Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:

=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))

Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function

James Brett

Hopefully the above makes sense

Jason Albrecht

Hi @James Brett

Would the following work for you?

=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication")

This is based on the syntax of the COUNTIFS Function

COUNTIFS( range1 criterion1 [ range2​criterion2​... ])

You'll note the reduction in brackets around the AND function, and there's no need to start the second (and subsequent) range/criterion with an AND.

James Brett

Hi Jason,

This is great thanks, seems to have worked. Just out of curiosity, is it possible to put another set of criteria in the equation, so for example, if the department involved had fabrication and design in the cell, as well just fabrication, or we also wanted to filter a project name in the 'project name and customer name' column?

kr

Jason Albrecht

Hi @James Brett

Yes, you can add ranges and criterion. For instance, regarding your first example, the formula might look something like:

=COUNTIFS([Date of Incident]:[Date of Incident], AND(@cell >= DATE(2023, 8, 1), @cell <= DATE(2024, 8, 1)), [Department Involved]:[Department Involved], "Fabrication", [Department Involved]:[Department Involved], AND("Fabrication", "Design"))

Note that I've not checked this formula. I've simply expanded the syntax provided in the COUNTIFS Function

James Brett

Ideal thanks Jason much appreciated,