CODEWITHSUNDEEP
excelcountif
Saket Nigam
Saket Nigam

Reputation: 7

Excel CountIfs with an or condition on Dates

Looking to try and find the following in Excel but am having issues getting this to work.

I have Dates in Column A which need to be checked against Dates in the Query Column to compare. I want to count all records where the comparison column is greater than the date in A5 or is an empty cell. There are other conditions that I will also want to check but cannot seem to get this to work.

=COUNTIFS(Query1[Purchased Date],OR(Query1[Purchased Date]="",Query1[Purchased Date]>A5))

Upvotes: 1

Views: 579

Answers (3)

Tom Sharpe
Tom Sharpe

Reputation: 34220

Perhaps this is more of a comment than an answer, but in this particular case you can just add the two separate totals:

=COUNTIF(Query1[Purchased Date],"")+COUNTIF(Query1[Purchased Date],">"&A5)

because the conditions are mutually exclusive.

Whether this helps or not depends on what additional criteria you want to add.

BTW there are two issues with your original formula:

(1) If you are combining lists of values with OR logic, you have to use addition instead (as in the two answers which use Sum or Sumproduct).

(2) The syntax of a Countif or Countifs where the range has to be kept separate from the criteria won't let you do what you want to do, so this again leads you to Sum or Sumproduct.

Upvotes: 0

Euben Monteiro
Euben Monteiro

Reputation: 11

You can use an array function instead of countif. Suppose your dates are in the range A2:A25 and your reference date is in cell B2.

If you type in any other cell

=SUM((A2:A25>B2)+(A2:A25=""))

and hit Ctrl+Shift+Enter it will give you the count you want.

This happens because Excel will resolve the first inner parentheses (A2:A25>B2) as an array of TRUE/FALSE, and does the same to the second (A2:A25=""). Next it will sum them, which is equivalent to the OR operation, and yields as a result an array of zeros (FALSE) and ones (TRUE). It wraps up by summing this array (with the function sum), all in one cell.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

use:

=SUMPRODUCT(--((Query1[Purchased Date]="")+(Query1[Purchased Date]>A5)>0))

Upvotes: 1

Related Questions