Google Sheets - COUNTIFS in ArrayFormula

Question

Project Completion Target Date	Estimated Completion Date
12/31/2020	12/24/2020
12/31/2020	1/7/2021
	1/19/2021
	2/15/2021

I'm trying to create a CountIfs statement, or a CountIf inside an Array, which will evaluate one column in relation to another column, repeated for multiple rows. With multiple conditions: If B > A, and ignore the row is A is empty.

Formula So Far:

=COUNTIF({B2:B5}, ">"&{A2:A5})

Result is 3, but the desired result is 1. This formula is counting when B > null = True. But desired result is when A is empty/null, then B !> A.

Ron M Ron M · Accepted Answer · 2020-12-14T23:02:45

Here's one option.

=COUNT(FILTER(A2:B5,NOT(ISBLANK(A2:A5)),B2:B5>A2:A5))/2

You can use FILTER() to filter your data with multiple conditions then count the filtered data (divide by 2 based on the number of columns in your data)

Your conditions:

A should not be empty
B should be greater than A

Example:

Filter the set of data based on the conditions required.

=FILTER(A2:B5,NOT(ISBLANK(A2:A5)),B2:B5>A2:A5)

Output:

It will return a filtered data like this. Then you just need to use COUNT() to count the filtered data. You just need to divide the result of the count based on the number of columns in your data.

Google Sheets - COUNTIFS in ArrayFormula

2 Answers