Conditional Sums

[CreativeRova]

Hi,

I have a spreadsheet. See below I need to count only rows that have the correct text in the same row and 1 row off.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Backs[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Combos[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]Combos[/TD]
[TD]Paid[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]Combos[/TD]
[TD]Paid[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]Client 3[/TD]
[TD]Backs[/TD]
[TD]Paid[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Bonus[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


I have asked before but am yet to get an answer. So in cell D1 I need to sum "paid" only from D6 downward if D6 = "backs" etc. Then the same for "bonus" if D = "Backs" then add up the "bonus" for that client with all the occurrences of this.

 

[Tetra201]

Try these:
D1 =SUMIFS($D$6:$D$11,$B$6:$B$11,$B1,$C$6:$C$11,$C1)
D2 =SUMIFS($D$6:$D$11,$B$5:$B$10,$B1,$C$6:$C$11,$C2)
D3 =SUMIFS($D$6:$D$11,$B$6:$B$11,$B3,$C$6:$C$11,$C3)
D4 =SUMIFS($D$6:$D$11,$B$5:$B$10,$B3,$C$6:$C$11,$C4)

 

[CreativeRova]

Hi Tetra,

that does work thanks, except for the bonus sum. it requires me to put in "backs" in B7 B9 etc. If that is the only way then fine.

 

[CreativeRova]

I just figure out it is because I have a header title. remove that and worked perfect.