CODEWITHSUNDEEP
excelexcel-formulaarray-formulascountif
John W
John W

Reputation: 21

COUNTIFS with AND & OR, Including < and > Operators

Column A contains dates. Column B contains dates and text values.

I'd like to write a formula that counts everything where (A:A <= DATE) AND (B:B > DATE OR B:B = "TextValue")

I know you can incorporate OR into countif using a formula like SUM(COUNTIF(A:A,{value1, value2})), but I have too many date values and want to use the > operator in the OR part.

I know this is a weird one with dates and text in the same column. Any help is appreicated.

Upvotes: 2

Views: 1754

Answers (1)

David Leal
David Leal

Reputation: 6769

I assume you want to count the number of rows that satisfy all the logical condition: A AND (C OR D), where each letter represents a logical condition. You can use in G2 the following:

=SUMPRODUCT( --(B2:B5<=E2), 
--((--(C2:C5 > E2) + (--(C2:C5 = DATEVALUE(E4)))) > 0) )

or using LET function to represent better each conditions:

=LET(cond1, --(B2:B5 <= E2), cond2, --(C2:C5 > E2), 
 cond3, --(C2:C5 = DATEVALUE(E4)),
 SUMPRODUCT(cond1, --((cond2 + cond3) > 0))
)

Here is the output:

sample excel file

The first row satisfies cond1, and the last row satisfies cond3.

Since you indicated the last condition is against a text value (E4) we need to convert it to a date using DATEVALUE.

SUMPRODUCT is expecting numeric values, so each logical condition needs to be converted to 1,0 values via --() operator. The OR condition is establish as a sum of two binary values and it should be greater than 0.

You can not use COUNTIFS in a scenario like this, because each criteria represents a simple comparison condition such as: "<="&E2 (for cond1 for example) internally it checks the condition: B2:B5 <= E2, i.e. a single logical condition where the left hand side appears only once. For an OR condition you need two logical conditions (i.e. OR(rng < a, rng > b)). In order to count a row all the conditions must be satisfied (logical AND), so there is no way to consider an OR condition with this function.

Upvotes: 1

Related Questions