+ Reply to Thread
Results 1 to 6 of 6

Sum up values based on different criteria in different columns

  1. #1
    Registered User
    Join Date
    03-15-2020
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    17

    Sum up values based on different criteria in different columns

    Hi friends,

    Please help me out to generate a generalised formula as an output for the set of data that I have attached here.

    To give a context, I want to sum up values of a column based on certain conditions from different columns and also based on an unique identifier.

    I have 8 persons, who has claim of the types which is mentioned as "Y" against each of the Person, based on certain conditions I generate a unique identifier, in this scenario it is P2P3P4P5 and now for this identifier I need get an output which is effectively the sum of amounts in the amount column but based on certain conditions.
    So basically I want to sum the amounts based on the persons in the unique identifier and remove any duplicates while adding
    In this scenario, my identifier is P2P3P4P5, so start with adding the amounts from P2 where it is "Y" and next look into P3 where it is "Y" but if the amount is already included as part of P2 neglect it and so on for P4 and P5.
    This is expected output and I'm need of a formula to generalise for different scenarios, by different I mean in certain scenarios the unique identifier could be P1P2P3P6 and the same logic of summing up the amounts to be utilised.

    Please help me out with this.

    Thanks & regards,
    Snehith
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,942

    Re: Sum up values based on different criteria in different columns

    one brute force method:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    however, I've no doubt this can be simplified with more thought.

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Sum up values based on different criteria in different columns

    how about trying this , no helper column required

    copy paste below J21 then hold control and shift then hit enther to make it array formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Registered User
    Join Date
    03-15-2020
    Location
    Sydney, Australia
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    17

    Re: Sum up values based on different criteria in different columns

    Thank you so much, just one more query, what if I want to have a condition one one more column of the data set, say for example if I have multiple assessments (A1, A2, A3, and so on) and the sum now should be based on each of the assessment number as well. Can you please help with this.

    Thanks & regards,
    Snehith

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,942

    Re: Sum up values based on different criteria in different columns

    in both cases you can add additional criteria by virtue of multiplication -- in an Array / Sumproduct - ORs are performed via Addition with ANDs via multiplication.

    e.g. SIGN((x=1)+(y=1)) would act like an OR whereas ((x=1)*(y=1)) would act like an AND

    So, given above, using the brute force SUMPRODUCT route (referenced rows 2:11) which relies on an OR for the P1:P8 testing:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    using the more elegant MMULT approach (referenced rows 2:12):
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by XLent; 04-01-2020 at 09:42 AM.

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Sum up values based on different criteria in different columns

    or simply all parts with multiplication try below with control shift and enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    in K18 keep the text you want to compare from A2 to A11

    or a little changed version to be used with control shift and enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by hemesh; 04-01-2020 at 03:32 PM.

+ 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. Calculate Values based on criteria in the columns
    By kollur in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2019, 08:10 PM
  2. Copy row values to columns based on criteria
    By kas.samrari in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2016, 03:25 PM
  3. [SOLVED] List values of 2 columns of a row based on criteria
    By ttinney in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2015, 10:15 AM
  4. [SOLVED] Copy/Paste multiple columns as values based on another columns criteria
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-28-2013, 06:50 AM
  5. Replies: 0
    Last Post: 09-15-2012, 02:56 AM
  6. matching values based on criteria and return values from another columns
    By lizard54 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2012, 11:29 AM
  7. Lookup values based on criteria in two columns
    By ola7mat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2012, 10:14 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