Excel Countif with spilled array,

Question

Hi
Bit puzzled,

countif with a spilled array, take below;

so I can get the result I want with helper;

but if I try and put it all in one I get an value error?

I have solved this by not using filter, but text join ( text split, and then re -splitting to get

so this I what I was trying for but using countif and filter

am I missing something obvious?

Richard.

Answer 1

Hi,

This simple formula in cell J4 works

=UNIQUE(FILTER(G4:G14,ISERROR(XMATCH(G4:G14,I4:I6))))

The next time you post a question, paste data in a format that can be pasted in an MS Excel file. Do not just post a screenshot.

Answer 2

Thanks, I've changed a bit,

=LET(alist,D5:D12,n,F5:F17,it,G5:G17,fil,FILTER(it,n = H5), XMATCH(alist,fil,0))

then isna to get a true false,

Thanks for the help,

RD

Answer 3

You are welcome.