+ Reply to Thread
Results 1 to 5 of 5

SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

  1. #1
    Registered User
    Join Date
    08-29-2017
    Location
    Oregon
    MS-Off Ver
    2016
    Posts
    26

    SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

    Hello,

    I am trying to SUM the total amount of requests my department receives in any given period of time (say, all of February). I have some duplicate entries (for example, see attached: A5, A7, & A8), but I only need the SUM of the first instance of the number requested (Column C). So in the case of the A5, A7, and A8 I should only see 10 requested, and not 30.

    Some of the Job Names (Column A) have duplicate entries because a user did not finish the entire request the first time, in this case the system creates a duplicate entry with the remaining number.


    Attached is a snapshot example of part of the data I am using. If for some reason more is required, let me know and I will populate the sheet with more data; I am simply including what I think is most necessary. I keep my data as its own tab in excel, and run my reports and formula in other tabs. So it leaves some room to work with.

    Thanks in advance for any tips on how to calculate!
    Attached Files Attached Files

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

    Hi

    Please try the below array formula:

    Please Login or Register  to view this content.
    to be confirmed by pressing CTRL+SHIFT+ENTER

    See the attached file.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-29-2017
    Location
    Oregon
    MS-Off Ver
    2016
    Posts
    26

    Re: SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

    That is a great start, thank you!

    However, I am wanting to SUM the number in Column C. So by what you posted, February should read 82 # Requested. Can I modify that code to SUMIF instead of COUNTIF?

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

    I managed to achieve this with the help of 2 helper columns, see the attached file.

    In E2:
    Please Login or Register  to view this content.
    In F2:
    Please Login or Register  to view this content.
    In J2:
    Please Login or Register  to view this content.

    I'll try to come up with a formula without the helper columns in a while and update it here.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-29-2017
    Location
    Oregon
    MS-Off Ver
    2016
    Posts
    26

    Re: SUMIF Job Entries Over Date Range + Ignore Duplicate Job Entries

    That works great! I was about to start making an array lookup for this based on a different structure, but this is much cleaner.

    Kudos your way!

+ 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] Ignore Duplicate Entries on (dynamic) Data Validation List
    By irmaosver in forum Excel General
    Replies: 2
    Last Post: 02-05-2014, 05:00 PM
  2. [SOLVED] Count duplicate text entries and ignore errors
    By raaz00 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 01:41 PM
  3. [SOLVED] SUMIF by ignoring Duplicate Entries in a different column
    By vij8y in forum Excel General
    Replies: 6
    Last Post: 08-16-2012, 08:18 PM
  4. Replies: 0
    Last Post: 06-14-2012, 12:38 PM
  5. Disallow duplicate entries using SUMIF
    By adamwestrop in forum Excel General
    Replies: 3
    Last Post: 08-01-2011, 04:33 PM
  6. Duplicate Entries into List of Unique Entries
    By MjRmatt in forum Excel General
    Replies: 1
    Last Post: 08-18-2010, 11:53 AM

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