Forum Discussion
How to categorize companies in Excel
Dear All, First off; thank you for taking the time to help me. I've been looking for the right fix for my problem but can't wrap my head around it. Solution: formula which categorizes companies, i...
Power Query as well as Visual Basic macro language are both parts of an Excel installation but they demand knowledge very different from the spreadsheet.
Using SUMIFS to find the total amount spent for each company mentioned in table Excel_1 will calculate the sum no matter their order respective tables.
If the amount is greater than zero, the IF statement should result in a letter, A or B.
The two are then concatenated using &.
=IF(SUMIFS(Excel_2[4. Amount of money spent];Excel_2[3. Company number ];[@[Company number]])=0;"";"A")
&
IF(SUMIFS(Excel_3[4. Amount of money spent];Excel_3[3. Company number ];[@[Company number]])=0;"";"B")
Excel_1 columns E to I calculates this step by step while col J makes it all in one formula.
If you are not using 'pyamas' tables, change the column references to your columns, eg cell J3 using ranges in other files like
=IF(SUMIFS([Book2]Sheet1!$D:$D;[Book2]Sheet1!$C:$C;C3)=0;"";"A")
&
IF(SUMIFS([Book3]Sheet1!$D:$D;[Book3]Sheet1!$C:$C;C3)=0;"";"B")
I will read your entire comment and try to apply it. Thank you for your time and of course the pictures you have provided. I appreciate the effort you took to replicate in excel that which i mentioned.