+ Reply to Thread
Results 1 to 5 of 5

SUMIFS and exclude Duplicates

  1. #1
    Registered User
    Join Date
    08-31-2015
    Location
    Indianapolis, IN
    MS-Off Ver
    2010
    Posts
    3

    SUMIFS and exclude Duplicates

    I am trying to use SUMIFS, but want to exclude duplicate values. I have the following formula:

    =SUMIFS(Table_POs[PO_BAL_AMT],Table_POs[Type of Service],"=Appraising Services",Table_POs[BUD_REF],"2015",Table_POs[Code],$E$2,Table_POs[PO_NUMBER],"<>Table_POs[PO_NUMBER]")

    My intent is to sum the values in the [PO_BAL_AMT] column where The [Type of Service], [BUD_REF], and [CODE] conditions are met, but exclude duplicate amounts. In the attached spreadsheet, there can be multiple entries for a single [PO_NUMBER] due to different [INVOICE_ID] numbers. I only want to sum the [PO_BAL_AMT] once for each [PO_NUMBER].

    I hope this makes sense and thank you for your help,
    Mark
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: SUMIFS and exclude Duplicates

    Try the attached. PO value column label should be PO_BAL_AMT

    Possible Answer 416566d1441043588-sumifs-and-exclude-duplicates-mark-sample.xlsx

    Pete

  3. #3
    Registered User
    Join Date
    08-31-2015
    Location
    Indianapolis, IN
    MS-Off Ver
    2010
    Posts
    3

    Re: SUMIFS and exclude Duplicates

    This works. However I may need some additional help.
    Is there a way do this without the helper columns? My final goal is to be able to calculate the remaining PO Balance amounts based on the code, service type, and budget year. Similar to the formulas that are in D65:D69.
    Thanks for your time and help, it is appreciated.

  4. #4
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: SUMIFS and exclude Duplicates

    Where do you need the PO result displayed?

  5. #5
    Registered User
    Join Date
    08-31-2015
    Location
    Indianapolis, IN
    MS-Off Ver
    2010
    Posts
    3

    Re: SUMIFS and exclude Duplicates

    It will be on a different tab. I didn't include it my original attachment because some of the information is sensitive. I should be able to copy a formula into the location, so if you show it in D65 I should be able to move it.

+ 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] Sum exclude duplicates if below each other
    By stimpy123 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-03-2014, 10:35 AM
  2. Sumifs exclude cells that are hidden with data filter
    By rhyan66 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2013, 10:16 AM
  3. SUMIFS and exclude Duplicates with a table reference
    By GOQC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-06-2013, 01:24 PM
  4. Replies: 1
    Last Post: 03-09-2013, 06:26 AM
  5. Sum column exclude duplicates
    By ryan@csi in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-18-2009, 02:40 PM
  6. Replies: 3
    Last Post: 11-12-2009, 04:42 PM
  7. Count 350 SS numbers, exclude duplicates
    By Marsha in forum Excel General
    Replies: 5
    Last Post: 03-07-2005, 02:06 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