+ Reply to Thread
Results 1 to 6 of 6

need help shortening formula

  1. #1
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    need help shortening formula

    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))

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: need help shortening formula

    =Sumproduct(--not(isna(match($D$1: $CM$1, C2:CK2,))))

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: need help shortening formula

    Does this work?

    =SUM(COUNTIF(D1:CM1,C2:K2))
    Array formula, use Ctrl-Shift-Enter
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: need help shortening formula

    both work thanks! Is there a way to also color the cells that match?
    Last edited by kevinu; 03-16-2017 at 12:02 PM.

  5. #5
    Registered User
    Join Date
    05-21-2014
    Posts
    92

    Re: need help shortening formula

    For the conditional formatting formula rule use (with range starting at cell C2):

    =COUNTIF($D$1:$CM$1,C2)>0

    Clarification: The above will give TRUE for cells that match the ones in $D$1:$CM$1, but will give FALSE for blank cells.
    Last edited by Monimonika; 03-16-2017 at 01:24 PM.

  6. #6
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    Re: need help shortening formula

    that did it thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula Shortening
    By KlawdStrife in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2014, 11:55 AM
  2. [SOLVED] Shortening a Formula
    By amasson in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-04-2014, 11:35 AM
  3. [SOLVED] Shortening a formula.
    By cle12000 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-23-2014, 12:03 PM
  4. Please help shortening this formula!!
    By chivalry2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-10-2013, 08:08 AM
  5. [SOLVED] Shortening a formula
    By GTVT06 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-19-2006, 09:50 PM
  6. [SOLVED] Shortening a formula
    By GTVT06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-19-2006, 06:25 PM
  7. [SOLVED] Shortening a formula
    By GTVT06 in forum Excel General
    Replies: 0
    Last Post: 07-19-2006, 12:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1