sum a column based on corresponding rows in another matched column

[CaptainJ]

Hello,

I have a May pay data set (as below) in a challenging format with one row per person (unfortunately it's not an option to change the format to allow easier formula). So I'm stuck trying to compile a formula to return, for a referenced pay type (nat pay, nat insurance, overtime, bonus or holiday pay), the sum of corresponding peoples FTE who have have been paid that pay type.

E.g. If any column headers C to G matches "Overtime" then sum corresponding FTE - the answer would equal 1.5 FTE, as Jane and Bill were paid Overtime).

Many thanks in advance for any assistance.

[TABLE="width: 537"]
<tbody>[TR]
[TD="colspan: 2"][TABLE="width: 537"]
<tbody>[TR]
[TD="colspan: 2"]May pay[/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]
[/TR]
[TR]
[TD][TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD]

C​

[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Full Time Equivalent (FTE)[/TD]
[TD]Nat Pay[/TD]
[TD]Nat Insurance[/TD]
[TD]Overtime[/TD]
[TD]Bonus[/TD]
[TD]Holiday pay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]1[/TD]
[TD]1500[/TD]
[TD]60[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Dave[/TD]
[TD]0.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Fred[/TD]
[TD]0.97[/TD]
[TD]1200[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bill[/TD]
[TD]0.5[/TD]
[TD]2500[/TD]
[TD]600[/TD]
[TD]-150[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jill[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Sarah[/TD]
[TD]0.95[/TD]
[TD]3000[/TD]
[TD]300[/TD]
[TD][/TD]
[TD]-50[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Bob[/TD]
[TD]1[/TD]
[TD]1200[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[svendiamond]

I think I'd need a bit more clarification to fully understand what you want. Is "overtime" the only thing you're worried about? If that's the case, then starting in H2 you could just fill down a simple:

=IF(NOT(ISBLANK(E2)),1.5,1)

Is that all you need? Your post isn't very specific, sorry. Trying to figure out what you mean.

 

[J.Ty.]

Hi,

Welcome to MrExcel.

I assume that the keyword ("Overtime" in the example above) is located in cell C12.
I see the following two options.

1. Faster, but with a longer formula:

=SUMIF(INDEX($D$3:$H$9,,MATCH(C12,$D$2:$H$2,0)),"<0",$C$3:$C$9)+SUMIF(INDEX($D$3:$H$9,,MATCH(C12,$D$2:$H$2,0)),">0",$C$3:$C$9)

2. Slower, but with a shorter formula:

=SUMPRODUCT(NOT(ISBLANK(INDEX($D$3:$H$9,,MATCH(C12,$D$2:$H$2,0))))*$C$3:$C$9)

Best,

J.Ty.

 

[CaptainJ]

Many thanks J.Ty., both are great and will save me a lot of time.

 

[J.Ty.]

You are welcome and good luck!

J.Ty.