+ Reply to Thread
Results 1 to 9 of 9

How to change the drag down fill formula?

  1. #1
    Registered User
    Join Date
    06-05-2017
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    3

    How to change the drag down fill formula?

    Hello, I have formula of "=SUMPRODUCT(--(A682:AD682=A1:AD1))".
    I want to drag down it to become "=SUMPRODUCT(--(A682:AD682=A2:AD2))".
    But when I drag down it became "=SUMPRODUCT(--(A683:AD683=A2:AD2))".
    How to change the drag down fill formula so it become like "=SUMPRODUCT(--(A682:AD682=A2:AD2)), =SUMPRODUCT(--(A682:AD682=A3:AD3))" and so on?
    Thanks for the help.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,888

    Re: How to change the drag down fill formula?

    Use the "$" symbol to create absolute references on what you want to lock. Your formula would then become:

    =SUMPRODUCT(--(A$682:AD$682=A1:AD1))

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: How to change the drag down fill formula?

    Try

    =SUMPRODUCT(--(A$682:AD$682=A1:AD1))

  4. #4
    Registered User
    Join Date
    06-05-2017
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    3

    Re: How to change the drag down fill formula?

    it worked, thank you so much everyone. ^^

  5. #5
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    687

    Re: How to change the drag down fill formula?

    Make the range to absolute reference, it freezes the range.

    By using the below formula, you can drag it to up, down, left and right.
    SUMPRODUCT(--($A$682:$AD$682=A1:AD1))

    By using the below formula, your value will change if you drag it left or right.
    =SUMPRODUCT(--(A$682:AD$682=A1:AD1))

    Now, choice is your's

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,888

    Re: How to change the drag down fill formula?

    Quote Originally Posted by nova312 View Post
    it worked, thank you so much everyone. ^^
    You're welcome. Glad we could help.

    If this solved your question, please mark this thread as SOLVED.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,746

    Re: How to change the drag down fill formula?

    Hi

    Make the A682:AD682 absulute. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However it looks an odd sort of use of a SUMPRODUCT. What is it you're doing with the formula?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,888

    Re: How to change the drag down fill formula?

    Edit: Removed duplicate post.

  9. #9
    Registered User
    Join Date
    06-05-2017
    Location
    Jakarta
    MS-Off Ver
    2016
    Posts
    3

    Re: How to change the drag down fill formula?

    Quote Originally Posted by Richard Buttrey View Post
    Hi

    Make the A682:AD682 absulute. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However it looks an odd sort of use of a SUMPRODUCT. What is it you're doing with the formula?
    Just to calculate multiple choice test score, thanks again everyone

+ 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. [SOLVED] Formula that will allow drag to fill row counts from all other worksheets
    By jts5814 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2016, 01:06 AM
  2. How to drag/fill a column with a 3D formula
    By BG23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2015, 07:03 AM
  3. [SOLVED] Drag fill not recognising formula pattern
    By steveneedshelp in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-12-2015, 09:06 AM
  4. [SOLVED] Can I make excel automatically drag-down a formula as you fill in data?
    By Yoshi64 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-16-2014, 07:28 PM
  5. Help With Correct Drag Formula In Order To Fill Blank Cells
    By Seán_Ireland in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2013, 05:43 PM
  6. Seconday Worksheet Reference Formula Drag Fill incrementing Issue
    By DavidCharles1224 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2010, 04:05 PM
  7. how to fill formula in any cell and drag it?
    By vumian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2006, 10:46 AM

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