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):

	A	B	C	D	E	F	G	H	I
1	MODULE 1	Session length	Staff1	Staff1 hrs	Staff2	Staff2 hrs	Staff3	Staff3 hrs	Total module teaching hours
2	Session 1	3	John	1	Bridget	1	Emma	1	3
3	Session 2	3	Amy	3	John	3	Emma	3	9
4	Session 3	3	Bob	1	Emma	3	Bridget	3	7

<colgroup><col style="width:48pt" span="9" width="80"> </colgroup><tbody>
</tbody>

CALCULATION SHEET (CAL):

	A	B	C	D	E
1		Teaching hours 1	Teaching hours 2	Teaching hours 3	Total payable hrs
2	Amy	3	0	0	3
3	Bob	1	0	0	1
4	Bridget	0	1	3	4
5	Emma	0	3	4	7
6	John	1	3	0	4

<colgroup><col span="2"><col span="2"><col><col></colgroup><tbody>
</tbody>

<colgroup><col style="width:48pt" span="2" width="80"> </colgroup><tbody>
</tbody>

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...

	A​	B​
1​		Total Payable Hrs​
2​	Amy​	3​
3​	Bob​	1​
4​	Bridget​	4​
5​	Emma​	7​
6​	John​	4​

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*