CODEWITHSUNDEEP
excelcountif
Xela
Xela

Reputation: 119

Excel COUNTIF() function won't accept a formula as range

The command I am trying to execute is:

=COUNTIF(UNIQUE(FILTER(A2:A2023,MONTH(C2:C2023)=9)), $E2)

I would like to simply check if E2 exists in the list.

UNIQUE(FILTER(A2:A2023,MONTH(C2:C2023)=9)) works fine, returning a list of numbers. However, wrapping it in the COUNTIF() makes excel tell me there is a problem with the formula. Alternatively, if there is another approach to checking if the element exists in the list, that would also work. Any help is appreciated.

Upvotes: 1

Views: 1751

Answers (2)

Scott Craner
Scott Craner

Reputation: 152465

COUNTIF will not take an array as a range. Just add the other criterion to the FILTER and check if there is a return:

=NOT(ISERROR(@FILTER(A2:A2023,(MONTH(C2:C2023)=9)*(A2:A2023=$E2))))

Or

=ISNUMBER(MATCH($E2,FILTER(A2:A2023,MONTH(C2:C2023)=9),0))

If you want a 1 or 0 response simply add -- to the formula: =--NOT(... or =--ISNUMBER(...

Upvotes: 1

Axuary
Axuary

Reputation: 1507

COUNTIF seems to need a pure cell reference in the first argument. You could put your UNIQUE formula in one cell (F2 for example) and then put =COUNTIF(F2#, $E2) in another. This Assumes you have Excel Office 365 that supports Dynamic Arrays.

Upvotes: 1

Related Questions