Sum Exact value & Split certain value between two cells

[Dtex20]

Hi guys,

I've got a problem, here is an example of the issue:

Cell A1:A3 value = 40
Cell B1:B3 Value = 100
Cell C1:C3 Value = 140

Cells D1 & D2 are SUM cells which add up the values.

I'm trying to find a way for D1 to SUM all the 40's
D2 to Sum all the 100's
Also i'm looking for a way to split 140 - 40 goes to D1 and 100 goes to D2.

The final value would be:

D1 = 240
D2 = 600

Can anyone point me in the right direction?

Thanks,
Connor

 

[Special-K99]

This is not clear for future use.
Will the values will only ever be 40 100 and 140 or something else ?
It's also not clear why 40 goes to D1 and 100 goes to D2 - is it because 140 is greater than 100 or something else?

Nevertheless this solves the example based on your given data.

in D1
=SUMIF(A1:C3,40)+COUNTIF(A1:C3,">100")*40

in D2
=SUMIF(A1:C3,100)+COUNTIF(A1:C3,">100")*100

 

[Dtex20]

This is not clear for future use.
Will the values will only ever be 40 100 and 140 or something else ?
It's also not clear why 40 goes to D1 and 100 goes to D2 - is it because 140 is greater than 100 or something else?

Nevertheless this solves the example based on your given data.

in D1
=SUMIF(A1:C3,40)+COUNTIF(A1:C3,">100")*40

in D2
=SUMIF(A1:C3,100)+COUNTIF(A1:C3,">100")*100

They will only ever be 40 and 100.

I've only given and example of the problem, i can manipulate what you've given me to suit my purpose.

Thanks,
Connor