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

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Basic[/td][td]Allowance[/td][td]Basic[/td][td]Tax[/td][td]Basic[/td][td][/td][td]Emp No.[/td][td]Basic[/td][/tr]


[tr][td]

2​

[/td][td]Emp No.[/td][td]Salary[/td][td]Accom[/td][td]Hol Pay[/td][td]Tax[/td][td]Temp Sal[/td][td][/td][td]

1​

[/td][td]

6350​

[/td][/tr]


[tr][td]

3​

[/td][td]

1​

[/td][td]

5000​

[/td][td]

1250​

[/td][td]

850​

[/td][td]

500​

[/td][td]

500​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]

2​

[/td][td]

5000​

[/td][td][/td][td]

0​

[/td][td]

500​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

5​

[/td][td]

3​

[/td][td]

8000​

[/td][td]

1250​

[/td][td]

0​

[/td][td]

800​

[/td][td]

800​

[/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]

4​

[/td][td]

5000​

[/td][td]

1250​

[/td][td]

1500​

[/td][td]

500​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In I2 enter:

=SUMIFS(INDEX($B$3:$F$6,MATCH($H2,$A$3:$A$6,0),0),$B$1:$F$1,I$1)<strike></strike>

 

[WardyKSA]

Peter - Sumproduct works a treat thank you very much! Ali and Aladin thanks also!!

 

[Oeldere]

Why not just use a helpcolumn?

g2=B2+D2+F2 and drag down.

 

[Peter_SSs]

Why not just use a helpcolumn?

g2=B2+D2+F2 and drag down.

I'm not sure how that would help complete the Journal Template layout required as shown in post #9?

 

[Oeldere]

after that the OP can easly use vloopup to get the data.

 

[AliGW]

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

You have added the $ sign where there is none in my formulae, which is why it's not working for you. Works fine here!

 

[Aladin Akyurek]

Peter - Sumproduct works a treat thank you very much! Ali and Aladin thanks also!!

SUMIF(S) work also a treat and faster too.

 

[AliGW]

The SUMPRODUCT formula doesn't work without adding the criteria by hand, it seems, unless I am misunderstanding.

 

[Peter_SSs]

The SUMPRODUCT formula doesn't work without adding the criteria by hand, it seems, unless I am misunderstanding.

From what I can see from post #9, there is a table at the top then a "Journal Template" that has the first & third columns completed & a formula is required for the second column. My formula just referenced the existing criteria in that bottom table.

SUMIF(S) work also a treat and faster too.

With over 1,000 rows calculating in approximately 1/100 of a second (on my 5-year-old machine) I suspect speed won't be too much of an issue. However, you are right that the SUMIFS is faster. If speed is an issue though, I would go for the array formula, for cell B10 in my post #10 layout:

=SUM(IF(A$3:A$6=A10,IF(B$1:F$1=C10,$B$3:$F$6))) Confirmed with Ctrl+Shift+Enter

Timed 5 times each over 1,200 rows of each formula, the average times for each for me were;

SUMPRODUCT____0.011286 seconds (100%)
SUMIFS(INDEX( __0.009834 seconds (87%)
SUM(IF__________0.00793 seconds (70%)