Summing totals from different sheet

[jwgreen1986]

so i currently have two sheets on a spreadsheet. one called active months, the other called payroll.

in the active months sheet i have 3 columns. one for each month of a quarter. so for example i currently have in there jan, feb and mar. i then have a list of employers with their start/end dates. in the active months it says yes/no as to whether the employee was active during them. so if someone left at the end of feb then it will say no in the mar column.

on the payroll sheet i have the employees listed again. this time there is a target and actual sale column per month. so actual and then target for jan actual and target for feb and so on all the way to dec.

what i want to do is look up all the active months values on the active months sheet and where it says yes then total up the the actuals for jan - mar. i only need the actual total for the 3 months combined. i will then also do another one for the targets.

so in short i need something that will lookup yes in the active months, which ever months have yes in it, then add up the totals for actuals for the member of staff. if i can do this then i can easily change the formula to add up the targets.

 

[oldbrewer]

	jan	feb	mar
alan	yes	yes	yes
bill	yes	no	no									pretend sheet 2
colin	yes	yes	yes
dave	yes	no	no
ed	yes	yes	yes							jan	jan	feb	feb	mar	mar
fred	yes	yes	yes							act	target	act	target	act	target
									alan	400	420	440	460	480	500
									bill	530	545	560	575	590	605
									colin	660	670	680	690	700	710
									dave	790	795	800	805	810	815
									ed	920	920	920	920	920	920
									fred	1050	1045	1040	1035	1030	1025
			march report
			sum actuals
		alan	1320
		bill
		colin	2040
		dave			is this what you want
		ed	2760
		fred	3120

<colgroup><col width="64" span="17" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[jwgreen1986]

hi, this is very very close to what i want. however it isnt adding the yes totals for bill and dave. even if a person has one or two yes i would like it to add up the totals for the months they have a yes in.