Sum multiple columns based on column and row criteria

[WardyKSA]

Hi all,
I need to be able to sum multiple columns based on the column header (to select the columns) but also particular rows based on the first column.

Please see the below example:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Basic[/TD]
[TD]Allowance[/TD]
[TD]Basic[/TD]
[TD]Tax[/TD]
[TD]Basic[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp No.[/TD]
[TD]Salary[/TD]
[TD]Accom[/TD]
[TD]Hol Pay[/TD]
[TD]Tax[/TD]
[TD]Temp Sal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5000[/TD]
[TD]1250[/TD]
[TD]850[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8000[/TD]
[TD]1250[/TD]
[TD]0[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5000[/TD]
[TD]1250[/TD]
[TD]1500[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to sum all the Basic columns for employee 1, which should return 6,350.

I'm having a complete mental block and can only think of using multiple vlookups.

Any help would be much appreciated

Thanks
W

 

[AliGW]

How many of these basic columns are there in the workbook and are they always in the same position?

 

[AliGW]

Is this too simple (copied down for each row)? If it's not what you are looking for, please add further detail.

=SUMIF(B$1:F$1,"Basic",B3:F3)

 

[AliGW]

Or perhaps this copied down:

=INDEX(SUMIF(B$1:F$1,"Basic",B3:F6),MATCH(A3,A3:A6),0)

 

[WardyKSA]

Hi Ali,
Currently all the columns should stay in the same place/order, but this could change.

Apologies I probably didn't give enough info originally but basically the table outlined above is a replica of our payroll system report. From this I then create a journal to post all the transactions into the finance system, which needs to be presented as a list.

The journal template looks like this (very simplified):
Currently the formula in the journal template states =if(column C = "Basic",vlookup emp code and return column 2), then again for column 4 and then column 6.

I just want 1 formula which looks up the emp code and returns the sum of columns based on the column header, it sounds so easy!! haha

[TABLE="width: 500"]
<tbody>[TR]
[TD]Emp No.[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6350[/TD]
[TD]Basic[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1250[/TD]
[TD]Accom[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500[/TD]
[TD]Tax[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5000[/TD]
[TD]Basic[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]Accom[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[TD]Overtime[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500[/TD]
[TD]Tax[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8800[/TD]
[TD]Basic[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1250[/TD]
[TD]Accom[/TD]
[/TR]
</tbody>[/TABLE]


Thanks
W

 

[AliGW]

So which layout are we working with? I provided a solution for the layout in your OP, but now it is presented entirely differently! Please state your case clearly so that respondents aren't wasting their time: currently, I am confused!

EDIT: summing by the column header is easy - see my solution. What isn't entirely clear to me is what you want. Is your latest table how you want the summary data presenting? If so, is there a reason why it has to be separated out into individual rows>

 

[AliGW]

These work:

=INDEX(SUMIF(B$1:F$1,"Basic",B3:F6),MATCH(A3,A3:A6),0)
=INDEX(SUMIF(B$2:F$2,"Accom",B3:F6),MATCH(A3,A3:A6),0)
=INDEX(SUMIF(B$2:F$2,"Tax",B3:F6),MATCH(A3,A3:A6),0)

You will need to tell us how you are calculating overtime.

EDIT: not sure that you have read beyond my first post to this thread!!!

 

[WardyKSA]

These work:

=INDEX(SUMIF(B$1:F$1,"Basic",B3:F6),MATCH(A3,A3:A6),0)
=INDEX(SUMIF(B$2:F$2,"Accom",B3:F6),MATCH(A3,A3:A6),0)
=INDEX(SUMIF(B$2:F$2,"Tax",B3:F6),MATCH(A3,A3:A6),0)

You will need to tell us how you are calculating overtime.

EDIT: not sure that you have read beyond my first post to this thread!!!

I have seen your above posts! Your index and match formula seems to work for employee 1 for Basic, Tax, Allowance, however when it gets to employee 2 and onwards it #REF!.

Ignore the overtime, I was just trying to replicate in the simplest format what it looks like (In reality the are many columns, not just Basic, Accomodation, Overtime, Tax).

The first table in my OP is the current data set which I am working on. The solution will be used in the second table which is the output. The second table needs to be in the list order unfortunately as this is how the journal is uploaded.

Is there a way to screenshot on this forum and I'll show you exactly the formulas I'm using?

This is the current formula which works on the first table but only for employee 1

=INDEX(SUMIF(B$1:F$1,C10,$B$3:$F$6),MATCH(A10,$A$3:$A$6),0)

C10 is a cell which states Basic
A10 is the employee code

 

[WardyKSA]

[TABLE="width: 783"]
<colgroup><col><col><col span="4"><col span="2"></colgroup><tbody>[TR]
[TD]Data Table (From payroll system)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Basic[/TD]
[TD]Allowance[/TD]
[TD]Basic[/TD]
[TD]Tax[/TD]
[TD]Basic[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp No.[/TD]
[TD]Salary[/TD]
[TD]Accom[/TD]
[TD]Hol Pay[/TD]
[TD]Tax[/TD]
[TD]Temp Sal[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5000[/TD]
[TD]1250[/TD]
[TD]850[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5000[/TD]
[TD] [/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8000[/TD]
[TD]1250[/TD]
[TD]0[/TD]
[TD]800[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5000[/TD]
[TD]1250[/TD]
[TD]1500[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Journal Template[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Critera 1[/TD]
[TD]Formula (Calculated)[/TD]
[TD]Critera 2[/TD]
[TD]Formula (detail)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6350[/TD]
[TD]Basic[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C10,$B$3:$F$6),MATCH(A10,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1250[/TD]
[TD]Allowance[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C11,$B$3:$F$6),MATCH(A11,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]500[/TD]
[TD]Tax[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C12,$B$3:$F$6),MATCH(A12,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]#REF![/TD]
[TD]Basic[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C13,$B$3:$F$6),MATCH(A13,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]#REF![/TD]
[TD]Allowance[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C14,$B$3:$F$6),MATCH(A14,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]#REF![/TD]
[TD]Tax[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C15,$B$3:$F$6),MATCH(A15,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]#REF![/TD]
[TD]Basic[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C16,$B$3:$F$6),MATCH(A16,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]#REF![/TD]
[TD]Allowance[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C17,$B$3:$F$6),MATCH(A17,$A$3:$A$6),0)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]#REF![/TD]
[TD]Tax[/TD]
[TD="colspan: 5"]=INDEX(SUMIF(B$1:F$1,C18,$B$3:$F$6),MATCH(A18,$A$3:$A$6),0)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is there a way to screenshot on this forum and I'll show you exactly the formulas I'm using?

Read my signature block below.


Try this formula, copied down.

Excel Workbook
	A	B	C	D	E	F	G
1		Basic	Allowance	Basic	Tax	Basic
2	Emp No.	Salary	Accom	Hol Pay	Tax	Temp Sal
3	1	5000	1250	850	500	500
4	2	5000		0	500	0
5	3	8000	1250	0	800	800
6	4	5000	1250	1500	500	0
7
8
9	Critera 1	Sum	Critera 2
10	1	6350	Basic
11	1	1250	Allowance
12	1	500	Tax
13	2	5000	Basic
14	2	0	Allowance
15	2	500	Tax
16	3	8800	Basic
17	3	1250	Allowance
18	3	800	Tax
19
Sum Based on Headings