I have been trying to create an array filter which compares two non number values by looking it up in a table (the original values are formed of a letter and a number "4A, 4B, 4C" and are given a numerical value in a table by a match"13,12,11").
Currently I am comparing both columns, which works, by the sum of an array formula:
{=SUM(IFERROR(IF(NUMBERVALUE(MATCH(Y4:Y94,GR,0))=NUMBERVALUE(MATCH(Z4:Z94,GR,0)),1,0),0))}
However, I want to be able to use filters in my table so values are ignored if they are hidden. When I use the aggregate to ignore hidden cells, I get the error "The value used is the wrong data type". I have included the formula below:
{=AGGREGATE(9,7,IFERROR(IF(NUMBERVALUE(MATCH(Y4:Y94,GR,0))=NUMBERVALUE(MATCH(Z4:Z94,GR,0)),1,0),0))}
The bold part is a direct copy from the previous formula. Because of this I am confused, looking at the formula it is returning 0's and 1's yet gives me the error #VALUE. Any ideas?
Thanks!!
Bookmarks