Summing totals from different sheet

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
janfebmar
alanyesyesyes
billyesnonopretend sheet 2
colinyesyesyes
daveyesnono
edyesyesyesjanjanfebfebmarmar
fredyesyesyesacttargetacttargetacttarget
alan400420440460480500
bill530545560575590605
colin660670680690700710
dave790795800805810815
ed920920920920920920
fred105010451040103510301025
march report
sum actuals
alan1320
bill
colin2040
daveis this what you want
ed2760
fred3120

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

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,653
Latest member
rishir

This Week's Hot Topics

Top