CODEWITHSUNDEEP
arraysgoogle-sheetscountif
Ken
Ken

Reputation: 1031

Google Sheets - COUNTIFS in ArrayFormula

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.

Upvotes: 1

Views: 1367

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

You can also use the following filter formula

=COUNTA(FILTER(B2:B7,A2:A7<>"",B2:B7>A2:A7))

enter image description here

Upvotes: 1

Kristkun
Kristkun

Reputation: 5963

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:

  1. A should not be empty
  2. 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.

enter image description here

Upvotes: 1

Related Questions