I was able to make my function work but how i did it seems inefficient and if i ever have to change something it will take a long time to edit it all. Is there a more simple way to make this work?
I'm trying to compare row A with row B. if the numbers in row A appear in row B then I want to count how many show up. All the numbers are unique so no overlap in values. As you an see Row A has 88 values and Row B has 44.
This is what worked but its HUGE.
=SUMPRODUCT((C2:CK2=$D$1)+(C2:CK2=$E$1)+(C2:CK2=$F$1)+(C2:CK2=$G$1)+(C2:CK2=$H$1)+(C2:CK2=$I$1)+(C2:CK2=$J$1)+(C2:CK2=$K$1)+(C2:CK2=$L$1)+(C2:CK2=$M$1)+(C2:CK2=$N$1)+(C2:CK2=$O$1)+(C2:CK2=$P$1)+(C2:CK2=$Q$1)+(C2:CK2=$R$1)+(C2:CK2=$S$1)+(C2:CK2=$T$1)+(C2:CK2=$U$1)+(C2:CK2=$V$1)+(C2:CK2=$W$1)+(C2:CK2=$X$1)+(C2:CK2=$Y$1)+(C2:CK2=$Z$1)+(C2:CK2=$AA$1)+(C2:CK2=$AB$1)+(C2:CK2=$AC$1)+(C2:CK2=$AD$1)+(C2:CK2=$AE$1)+(C2:CK2=$AF$1)+(C2:CK2=$AG$1)+(C2:CK2=$AH$1)+(C2:CK2=$AI$1)+(C2:CK2=$AJ$1)+(C2:CK2=$AK$1)+(C2:CK2=$AL$1)+(C2:CK2=$AM$1)+(C2:CK2=$AN$1)+(C2:CK2=$AO$1)+(C2:CK2=$AP$1)+(C2:CK2=$AQ$1)+(C2:CK2=$AR$1)+(C2:CK2=$AS$1)+(C2:CK2=$AT$1)+(C2:CK2=$AU$1)+(C2:CK2=$AV$1)+(C2:CK2=$AW$1)+(C2:CK2=$AX$1)+(C2:CK2=$AY$1)+(C2:CK2=$AZ$1)+(C2:CK2=$BA$1)+(C2:CK2=$BB$1)+(C2:CK2=$BC$1)+(C2:CK2=$BD$1)+(C2:CK2=$BE$1)+(C2:CK2=$BF$1)+(C2:CK2=$BG$1)+(C2:CK2=$BH$1)+(C2:CK2=$BI$1)+(C2:CK2=$BJ$1)+(C2:CK2=$BK$1)+(C2:CK2=$BL$1)+(C2:CK2=$BM$1)+(C2:CK2=$BN$1)+(C2:CK2=$BO$1)+(C2:CK2=$BP$1)+(C2:CK2=$BQ$1)+(C2:CK2=$BR$1)+(C2:CK2=$BS$1)+(C2:CK2=$BT$1)+(C2:CK2=$BU$1)+(C2:CK2=$BV$1)+(C2:CK2=$BW$1)+(C2:CK2=$BX$1)+(C2:CK2=$BY$1)+(C2:CK2=$BZ$1)+(C2:CK2=$CA$1)+(C2:CK2=$CB$1)+(C2:CK2=$CC$1)+(C2:CK2=$CD$1)+(C2:CK2=$CE$1)+(C2:CK2=$CF$1)+(C2:CK2=$CG$1)+(C2:CK2=$CH$1)+(C2:CK2=$CI$1)+(C2:CK2=$CJ$1)+(C2:CK2=$CK$1)+(C2:CK2=$CL$1)+(C2:CK2=$CM$1))
Bookmarks