Hi,
Looking help in constructing a formula to help me interrogate a large data area which if 2 criteria or met then sum the associated hours worked.
Basically I want a summary report that gives me the total hours worked by a particular grade for each month as we go through the year.
Below is the summary table I am looking to complete, aong with 2 formula's I tried without success.
<colgroup><col width="64" style="width: 48pt;" span="5">
<col width="18" style="width: 14pt; mso-width-source: userset; mso-width-alt: 658;">
<col width="451" style="width: 338pt; mso-width-source: userset; mso-width-alt: 16493;">
<tbody>
</tbody>
Below is a summary of the main data tab - note all columns are named ranges.
Really appreciate any help or advice provided.
Thanks in advance.
Looking help in constructing a formula to help me interrogate a large data area which if 2 criteria or met then sum the associated hours worked.
Basically I want a summary report that gives me the total hours worked by a particular grade for each month as we go through the year.
Below is the summary table I am looking to complete, aong with 2 formula's I tried without success.
Current Month Summary | ||||||
Band 2 | Band 5 Ordinary | Band 5 SNG | Total Hours | |||
XR02 | Other | SCOT G | ||||
Apr | #N/A | 0 | 0 | #N/A | =SUMIF(Mth,A7,INDEX(Grade,0,MATCH(B6,SUM(Time_Converted),0))) | |
May | #VALUE! | 0 | 0 | #VALUE! | '=SUMPRODUCT(Mth=$A$8)*(Grade=$B$6)*(Time_Converted) | |
Jun | 0 | 0 | 0 | 0 | ||
Jul | 0 | 0 | 0 | 0 | ||
Aug | 0 | 0 | 0 | 0 | ||
Sep | 0 | 0 | 0 | 0 | ||
Oct | 0 | 0 | 0 | 0 | ||
Nov | 0 | 0 | 0 | 0 | ||
Dec | 0 | 0 | 0 | 0 | ||
Jan | 0 | 0 | 0 | 0 | ||
Feb | 0 | 0 | 0 | 0 | ||
Mar | 0 | 0 | 0 | 0 | ||
#N/A | 0 | 0 | #N/A |
Below is a summary of the main data tab - note all columns are named ranges.
Really appreciate any help or advice provided.
Thanks in advance.