+ Reply to Thread
Results 1 to 5 of 5

Sum multiple criteria with 0 to 3 cells helper only.

  1. #1
    Registered User
    Join Date
    03-09-2017
    Location
    Macau
    MS-Off Ver
    2007
    Posts
    7

    Sum multiple criteria with 0 to 3 cells helper only.

    Hi Everyone,
    I am learning very hard about excel and motivated with a file to present in my work.may i ask some experts about this problem?

    " If F cell has a text "recon" in it, next question is , is the ID in the column E unique or duplicated? If duplicated then the formula should be able to look at the G column and return the max qty of all duplicated value, ignoring the lesser value. Once it all done then sum all the max unique that has "recon" text in it. A row must consists of unique ID in E column, has recon text in F column and the qty is maximum among the cells in G column. Then all will be consolidated and summed up, ignoring duplicated and lesser value. Please see sample table below with 97 as the sum total of all the unique row that has "recon text in it: Please help to do this with 0 or maximum 3 cell helper, as my spreadsheet has multiple text to use as criteria and adding one column per each text criteria is not practical and will consume huge space.

    E F G
    10-12345. Reconstruction of plate 12
    10-12345. Reconstruction of plate 34
    10-37893. Reconstruct edge 37
    10-76542. Reconstruction leg 26
    10-12345. Reconstruction of plate 21
    Sum Total of unique row 97

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum multiple criteria with 0 to 3 cells helper only.

    Hi Welcome to Forum,
    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    03-09-2017
    Location
    Macau
    MS-Off Ver
    2007
    Posts
    7

    Re: Sum multiple criteria with 0 to 3 cells helper only.

    Hi Ankur,
    Please see attached. many thanks
    Attached Files Attached Files

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Sum multiple criteria with 0 to 3 cells helper only.

    Try

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


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  5. #5
    Registered User
    Join Date
    03-09-2017
    Location
    Macau
    MS-Off Ver
    2007
    Posts
    7

    Re: Sum multiple criteria with 0 to 3 cells helper only.

    Hi Ankur,
    the formula did not work out as intended to. although the F column has the text string I am looking for the G 8 retuned 0. kindly see attached new work book sample for your reference and assistance.
    Thanks,
    Attached Files Attached Files

+ 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. Sort by helper column even if values in helper are alike
    By NoSleepForMe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-10-2016, 04:14 AM
  2. [SOLVED] computations within conditional criteria list extraction - eliminating helper columns
    By Nerpilis in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-15-2015, 02:21 PM
  3. multiple helper columns
    By ammartino44 in forum Excel General
    Replies: 6
    Last Post: 03-25-2015, 02:52 PM
  4. Best performance option for helper cells
    By Gingermuppet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2014, 07:23 PM
  5. [SOLVED] Week No. Vs.Value without Helper cells in Column E
    By thilag in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2013, 04:31 AM
  6. [SOLVED] SUMIFS and Date Criteria (Month Year etc...) but without helper column?
    By JungleJme in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-16-2013, 07:21 AM
  7. [SOLVED] Help Consolidating Multiple Formulas in to 1 cell (No Helper Columns)
    By twiggywales in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-26-2012, 03:38 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