Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
I have three columns..
A | B | Amount |
1000 | 25 | 50 |
32 | 1000 | 82 |
1500 | 78 | 90 |
1300 | 89 | 54 |
25 | 1300 | 60 |
I need to sum up the Amount when Column A = Column B. eg: First 2 rows, 1000 is in both columns but has different adjacent values, so i have to sum up 50 + 82 and similarly Row 4 and 5, since 1300 is in both columns need to sum up, 60 + 54. whereas row 3, is a single unique line item and that remains the same. Plz help me find the right expression.
=if(FieldIndex('B',sum(A))=0,if(FieldIndex('A',sum(B))=0,Bal), Bal + FieldValue('Bal',FieldIndex('B',sum(A))))
A | B | Amount | = |
---|---|---|---|
1000 | 25 | 50 | 132 |
32 | 1000 | 82 | 132 |
1500 | 78 | 90 | 90 |
1300 | 89 | 54 | 114 |
25 | 1300 | 60 | 114 |
right?
in this case?
A | B | Amount | = |
---|---|---|---|
1000 | 1400 | 31 | 96? |
1400 | 78 | 32 | 96? |
89 | 1000 | 33 | 96? |
Hi,
Can you do an applymap function in the script level between A & B to get the amount correspondingly ,
and call this calculated amount in the expression directly
Regards,
Kiruthiga
the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B.
Hi, Try (Amount)+FieldValue('Amount',FieldIndex('B',sum(A))) Please see file attached. Regards Rahul
Thanks.. It surely works.. but the first row has a value 110.. Why is that?
Thanks & Regards,
Shilpa
A 25 =60 B 25 =50 Therefore value equals 60+50 = 110
"the second case can never happen cause for each unique number in column A, there can be only on corresponding line item in B." is not correct...?
Hi Rahul,
Your expression is giving me th required output but it is not working in my application.
Hi Shilpa, If possible, could you post an example? Regards R