CODEWITHSUNDEEP
excelvbaexcel-formuladatatable
fanglies
fanglies

Reputation: 39

How to specify conditions for excel COUNTIFS function?

What I am trying to do is count the number of values in an excel column named "Report Deadline" that are after today, but also have no value(blank) or have the value "N/A" in another column named "Date Report Issued"

I am unfamiliar with how to specify conditions for the COUNTIFS function but I also don't know if there is a better way to do it rather than use COUNTIFS

This is what I have

=COUNTIFS(Table22[Report Deadline],">=" & TODAY(),Table22[Date Report Issued],"=" "",Table22[Date Report Issued],"=" "N/A")

Upvotes: 1

Views: 76

Answers (1)

Gravitate
Gravitate

Reputation: 3064

You can't really have an "OR" condition in a COUNTIFS. I would suggest doing two separate COUNTIFS, and then adding them together. Like so:

=COUNTIFS(Table22[Report Deadline],">=" & TODAY(),Table22[Date Report Issued], "")
+
COUNTIFS(Table22[Report Deadline],">=" & TODAY(),Table22[Date Report Issued], "N/A")

If you really wanted to do it in a single expression, you could put "N/A" and "" into an array and wrap the COUNTIFS in a SUM. But it would essentially be doing the same thing as the two separate functions and arguably not be as easy to read:

=SUM(COUNTIFS(Table22[Report Deadline],">=" & TODAY(),Table22[Date Report Issued],{"","N/A"}))

Upvotes: 2

Related Questions