Simplify ISNUMBER MATCH formula

[BWMagee]

Hi, I currently have the following formula, which returns the correct result:

=
--ISNUMBER(MATCH(AL26,AK$26:AK$169,0))+ISNUMBER(MATCH(AL26,AJ$26:AJ$169,0))+ISNUMBER(MATCH(AL26,AI$26:AI$169,0))+ISNUMBER(MATCH(AL26,AH$26:AH$169,0))+ISNUMBER(MATCH(AL26,AG$26:AG$169,0))+ISNUMBER(MATCH(AL26,AF$26:AF$169,0))+ISNUMBER(MATCH(AL26,AE$26:AE$169,0))+ISNUMBER(MATCH(AL26,AD$26:AD$169,0))+ISNUMBER(MATCH(AL26,AC$26:AC$169,0))+ISNUMBER(MATCH(AL26,AB$26:AB$169,0))+ISNUMBER(MATCH(AL26,AA$26:AA$169,0))+ISNUMBER(MATCH(AL26,Z$26:Z$169,0))+ISNUMBER(MATCH(AL26,Y$26:Y$169,0))+ISNUMBER(MATCH(AL26,X$26:X$169,0))+ISNUMBER(MATCH(AL26,W$26:W$169,0))+ISNUMBER(MATCH(AL26,V$26:V$169,0))+ISNUMBER(MATCH(AL26,U$26:U$169,0))+ISNUMBER(MATCH(AL26,T$26:T$169,0))+ISNUMBER(MATCH(AL26,S$26:S$169,0))+ISNUMBER(MATCH(AL26,R$26:R$169,0))+ISNUMBER(MATCH(AL26,Q$26:Q$169,0))+ISNUMBER(MATCH(AL26,P$26:P$169,0))+ISNUMBER(MATCH(AL26,O$26:O$169,0))+ISNUMBER(MATCH(AL26,N$26:N$169,0))+ISNUMBER(MATCH(AL26,M$26:M$169,0))+ISNUMBER(MATCH(AL26,L$26:L$169,0))+ISNUMBER(MATCH(AL26,K$26:K$169,0))+ISNUMBER(MATCH(AL26,J$26:J$169,0))+ISNUMBER(MATCH(AL26,I$26:I$169,0))+ISNUMBER(MATCH(AL26,H$26:H$169,0))+ISNUMBER(MATCH(AL26,G$26:G$169,0))

Here is what it says: Any time the value in AL26 is found in a range, return 1. Anytime it isn't, return 0. Add them all together.
Each range is the same number of rows, and one column across from the last. There are 31 columns.

The problem is I need to apply this formula to about 70 tables, with the same columns, but different rows. Each table is underneath the last. If I were to copy this formula down 70 times, I would have to change each range individually. 31 columns * 70 tables = 2170 manual changes I would need to make. That would be very tedious, so I'm wondering if there is a way to simplify the above formula to give the same result?

It not, I might have to put each table in its own tab. Thus creating 70 new tabs. Doing that would allow me to keep the above formula the same in each table.
All suggestions are much appreciated.

 

[Michael M]

Without replicating the entire formula set, couldn't you use something like

=COUNTIF(AA26:AK169,AL26)+COUNTIF(G26:Z169,AL26)

 

[Ron Coderre]

I'm fairly sure this regular formula returns the count that you want:

=SUM(--(FREQUENCY(IF((G26:AK169=AL26),COLUMN(G26:AK169)),COLUMN(G26:AK26))>0))

Is that something you can work with?

 

[BWMagee]

Thanks for the reply, both. I wish to do this without VBA. I have decided to take the path of putting each new table in a new tab.

 

[Michael M]

Both attempts at solutions aren't VBA....Have I missed something ???

 

[Rick Rothstein]

Both attempts at solutions aren't VBA....Have I missed something ???

I think the OP was thrown because both formulas are shown in comment boxes labeled "VBA Code".

 

[Michael M]

....good point Rick !!