# Sum up values based on different criteria in different columns

1. ## 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.

Thanks & regards,
Snehith

2. ## Re: Sum up values based on different criteria in different columns

one brute force method:

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

3. ## 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:
`Please Login or Register  to view this content.`

4. ## 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. ## 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:
`Please Login or Register  to view this content.`
using the more elegant MMULT approach (referenced rows 2:12):
Formula:
`Please Login or Register  to view this content.`

6. ## 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:
`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:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)