Sumif from multiple columns without helper column

[angeloudaki]

Hi.

I have been looking into this but (again) getting very confused as there is a lot of information that just doesn't do what I want - it seems.

Basically, I have two worksheets in one workbook; one sheet holds the variable data, the other holds the calculations (I do not yet know how to use Pivot tables but I am awaiting a session). Simplified example:

• I have up to three staff members teaching on any one module over three sessions
• Each staff member has variable payable teaching hours logged

My situation:

• the session is 3hrs long
• Session one - there are 3 tutors each teaching for one hour (total payable teaching hours = 3)
• Session two - there are 3 tutors each teaching for the full 3hrs each (total payable teaching hours = 9)
• Session three - there are 3 tutors each teaching different hrs (total teaching hours = 7)

VARIABLE DATA SHEET (VAR):
[TABLE="width: 720"]
<colgroup><col style="width:48pt" span="9" width="80"> </colgroup><tbody>[TR]
[TD="class: xl67, width: 80"][/TD]
[TD="class: xl67, width: 80"]A
[/TD]
[TD="class: xl67, width: 80"]B
[/TD]
[TD="class: xl68, width: 80"]C
[/TD]
[TD="class: xl68, width: 80"]D
[/TD]
[TD="class: xl68, width: 80"]E
[/TD]
[TD="class: xl68, width: 80"]F
[/TD]
[TD="class: xl68, width: 80"]G
[/TD]
[TD="class: xl68, width: 80"]H
[/TD]
[TD="class: xl68, width: 80"]I
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 80"]1
[/TD]
[TD="class: xl67, width: 80"]MODULE 1
[/TD]
[TD="class: xl67, width: 80, align: center"]Session length
[/TD]
[TD="class: xl68, width: 80"]Staff1[/TD]
[TD="class: xl68, width: 80, align: center"]Staff1 hrs
[/TD]
[TD="class: xl68, width: 80"]Staff2[/TD]
[TD="class: xl68, width: 80, align: center"]Staff2 hrs
[/TD]
[TD="class: xl68, width: 80"]Staff3[/TD]
[TD="class: xl68, width: 80, align: center"]Staff3 hrs
[/TD]
[TD="class: xl68, width: 80, align: center"]Total module
teaching hours
[/TD]
[/TR]
[TR]
[TD="class: xl67"]2
[/TD]
[TD="class: xl67"]Session 1[/TD]
[TD="class: xl67, align: center"]3
[/TD]
[TD="class: xl67"]John[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67"]Bridget[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67"]Emma[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67, align: center"]3
[/TD]
[/TR]
[TR]
[TD="class: xl67"]3
[/TD]
[TD="class: xl67"]Session 2[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67"]Amy[/TD]
[TD="class: xl67, align: center"]3
[/TD]
[TD="class: xl67"]John[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67"]Emma[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]9[/TD]
[/TR]
[TR]
[TD="class: xl67"]4
[/TD]
[TD="class: xl67"]Session 3[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67"]Bob[/TD]
[TD="class: xl67, align: center"]1[/TD]
[TD="class: xl67"]Emma[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67"]Bridget[/TD]
[TD="class: xl67, align: center"]3[/TD]
[TD="class: xl67, align: center"]7[/TD]
[/TR]
</tbody>[/TABLE]

CALCULATION SHEET (CAL):

[TABLE="width: 792"]
<colgroup><col span="2"><col span="2"><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]Teaching hours 1[/TD]
[TD]Teaching hours 2[/TD]
[TD]Teaching hours 3[/TD]
[TD]Total payable hrs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Amy[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bob[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bridget[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Emma[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]John[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 160"]
<colgroup><col style="width:48pt" span="2" width="80"> </colgroup><tbody> [TR]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]
To do this I have three helper columns and in each column I use the (equivalent) formula: =SUMIF('VAR'!C:C,A2,'VAR'!D:D) then hide the helper columns and sum the values returned (above). I believe this translates as if data in (VAR)C:C matches (CAL)A2 then sum the corresponding values in (VAR)D:D.

I don't want to use helper columns. So, essentially what I want to do is the same but calculating data in one go i.e. (CAL)C:C E:E G:G matches (CAL)A2 then sum the corresponding values in (VAR)D:D F:F H:H........ does that make sense??

 

[wideboydixon]

Formula for CAL!E2 (or CAL!B2 if you want to move everything to the left):

=SUMIF('VAR'!$C:$C,$A2,'VAR'!$D:$D)+SUMIF('VAR'!$E:$E,$A2,'VAR'!$F:$F)+SUMIF('VAR'!$G:$G,$A2,'VAR'!$H:$H)

It's basically what you have already; you just need to put "+" signs between the three formulas.

WBD

 

[Marcelo Branco]

Or maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

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

Total Payable Hrs​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Amy​

[/td][td]

3​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td]

1​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Bridget​

[/td][td]

4​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Emma​

[/td][td]

7​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

John​

[/td][td]

4​

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

Formula in B2 copied down
=SUMIF(VAR!C:G,A2,VAR!D:H)

Hope this helps

M.

 

[angeloudaki]

Wowsers that was easy then! *feeling foolish* thank you so much! *hands you a box of chocolates each*