+ Reply to Thread
Results 1 to 2 of 2

SUMIF Formula - dragging down but need to skip several rows.

  1. #1
    Registered User
    Join Date
    09-10-2019
    Location
    Indianapolis, IN
    MS-Off Ver
    Windows 7
    Posts
    1

    SUMIF Formula - dragging down but need to skip several rows.

    I'm working on a vacation schedule at work. I have a formula that sums the associates hours for each day, and subtracts out things like vacation time, projects, meeting time and also adds back in overtime. I have a formula that works. However, I need to drag the formula down by thousands of rows and I'm unable to do this because there are 15 rows in between each "Vacation Total" row. This is the formula I am using - =(SUMIF($E$3:$E$7,$D$850,$F3:$F7))-(F850+F851+F853+F854+F855)+F852

    It's difficult to explain so I'm attaching a copy of the Excel file. I've manually updated the formulas on Vacation Total rows up through row 712 to reflect the correct row for the correct dates, but would really love to be able to drag the pattern down and avoid all of the hours I will spend manually updating the schedule. Thank you in advance!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: SUMIF Formula - dragging down but need to skip several rows.

    I think only VBA will be helpful.
    For this you need to improve the formula in F24. It doesn't work well with copying.
    The correct formula in F24.
    PHP Code: 
    =(SUMIF($E$3:$E$7,$D18,F$3:F$7))-(F18+F19+F21+F22+F23)+F20 
    Copy the code to the VBA normal module and run.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by maras_mak; 09-11-2019 at 02:55 AM.
    Best Regards,
    Maras.

+ 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] If Text is within range, return value & skip number of rows when dragging out formula
    By Vihral in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-27-2018, 04:49 AM
  2. Dragging a formula problem (Needs skip a certain column)
    By mutzie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-10-2016, 03:31 AM
  3. How to skip rows when dragging a formula
    By casparschwa in forum Excel General
    Replies: 3
    Last Post: 10-02-2016, 06:00 PM
  4. skip 1 cell when dragging down formula
    By excelhelpneeds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2014, 01:30 PM
  5. How do you skip a row when auto dragging a formula?
    By gmatula89 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 01:08 PM
  6. [SOLVED] Skip column when dragging formula.
    By D_N_L in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2012, 07:42 AM
  7. Skip every other line while dragging formula
    By dlswa in forum Excel General
    Replies: 2
    Last Post: 03-05-2012, 09:31 PM

Tags for this Thread

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