Search code examples
excelexcel-formula

How to sum multiple columns with multiple conditions

I have a table as below:

COA 100 101 102 200 201 202 300
11000000000 1000 1200 1100 1000 1200 1100 1800
11200100000 1500 1400 1300 1500 1400 1300 1700

I want to sum 100, 101, 102 to one group and 200, 201, 202 to one group and 300 to one group. Below is my expected Output:

First Table:

COA 100+ 200+ 300
11000000000 3300 3300 1800
11200100000 4200 4200 1700

Second Table: Assuming that the 2 COAs belong to 1 big COA and I want to add the totals of both those big COAs by group.

COA 100+ 200+ 300
11000000000 & 11200100000 7500 7500 3500

How can I get expected Output. Thank you.

Solution

  • For the first table: =SUM(IF((LEFT($B$1:$H$1,1)=LEFT(B$15,1))*($A16=$A$2:$A$3),$B$2:$H$3,""))

    This formula can be dragged right and down as needed.

    Refer to the image below to see what each arguments contains/refers to.

    Formula Table1 Output Table1

    For Table 2: =SUM(IF((LEFT($B$1:$H$1,1)=LEFT(B19,1))*((CHOOSEROWS(TEXTSPLIT($A20,," & "),1)*1=$A$2:$A$3)+(CHOOSEROWS(TEXTSPLIT($A20,," & "),2)*1=$A$2:$A$3)),$B$2:$H$3,""))

    Here you have to make sure that your COA is always formatted the same: ### & ###.

    Formula Table2 Output Table2

    I suspect there might be an easier/better way to do Table 2, but it works.