Excel formula for conditional subtraction
Solved
geo001969
Posted messages
5
Status
Membre
-
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Vaucluse Posted messages 27336 Registration date Status Contributeur Last intervention -
Good evening everyone,
I've been testing everything for 5 hours without being able to solve this conditional subtraction problem.
I want to compare the year-on-year evolution in euros of the amount of my purchases from my suppliers.
I have columns A and B for 2012, and columns C and D for 2013, and the evolution for 2013 in column E.
A B C D E
2012 2012 2013 2013 2013
Supplier Name Amount Supplier Name Amount Evolution
Supplier1 100 Supplier2 200 -200
Supplier2 400 Supplier5 150 none
I am looking to subtract the values in columns B and D, that is, B-D, with the link being the supplier names in columns A and C. In this example, the only supplier in column C that is present in column A is Supplier2, but not necessarily on the same line (a supplier from 2013 is not always a supplier in 2012).
With =SUMIF(A:A;C1...) or =COUNTIF(A:A;C1), I can establish the link on the supplier's name. But how do I then calculate E by subtracting column B-D? In the example, 400-200 by linking it to the value Supplier2, but in rows 1 and 2.
Thank you for your valuable help.
I've been testing everything for 5 hours without being able to solve this conditional subtraction problem.
I want to compare the year-on-year evolution in euros of the amount of my purchases from my suppliers.
I have columns A and B for 2012, and columns C and D for 2013, and the evolution for 2013 in column E.
A B C D E
2012 2012 2013 2013 2013
Supplier Name Amount Supplier Name Amount Evolution
Supplier1 100 Supplier2 200 -200
Supplier2 400 Supplier5 150 none
I am looking to subtract the values in columns B and D, that is, B-D, with the link being the supplier names in columns A and C. In this example, the only supplier in column C that is present in column A is Supplier2, but not necessarily on the same line (a supplier from 2013 is not always a supplier in 2012).
With =SUMIF(A:A;C1...) or =COUNTIF(A:A;C1), I can establish the link on the supplier's name. But how do I then calculate E by subtracting column B-D? In the example, 400-200 by linking it to the value Supplier2, but in rows 1 and 2.
Thank you for your valuable help.
2 réponses
Hello.
An Excel file with a numerical example would have been more practical than the series of data you tried to type in your message...
Go to https://www.cjoint.com/ to create the link, which you will come back and paste here.
Does each supplier only appear once in each column?
In the meantime, I suggest this method, which uses your SUMIF function:
Formula to enter in E3 and copy down
=SUMIF($C:$C,C3,$D:$D)-SUMIF($A:$A,C3,$B:$B)
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
An Excel file with a numerical example would have been more practical than the series of data you tried to type in your message...
Go to https://www.cjoint.com/ to create the link, which you will come back and paste here.
Does each supplier only appear once in each column?
In the meantime, I suggest this method, which uses your SUMIF function:
Formula to enter in E3 and copy down
=SUMIF($C:$C,C3,$D:$D)-SUMIF($A:$A,C3,$B:$B)
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂
