Creating a Summary Table based on Client Work and Resource Name

Stevefc

I need to get a summary report to show the resource name against both the client work and non-client work with total hours

Example results:

I can get the totals using =SUMIF([Resource Name]1:[Resource

Name]17, [Field 1]#, [Hours - Client]1:[Hours - Client]17).

Can I use Index/Match to get my summary table, please advise? 

Regards Steve

Genevieve P.

Hi Steve,

If you're looking to build this in another sheet, you can actually just use a similar SUMIF formula, but use cross-sheet references instead of column references, and use SUMIFS (plural) instead, since you'll have more than one criteria to look for.

For example, if your Grid has the exact information as you've showed above, then in the very first Hrs cell, you can input a formula like this:

=SUMIFS({Hours - Client Column}, {Resource Name Column}, Resource@row, {Client Work Column}, [Client Work]@row)

You'll notice that for a SUMIFS plural, we first list the column we want to SUM, then we list the columns & criteria afterwards. This formula could then be drag-filled (see here) down the whole column in your chart and it will auto-populate based on what's in the Resource column and the Client Work column in your current chart.

To make the second formula for the non-client work, all you have to do is adjust the second column & second criteria:

=SUMIFS({Hours - Client Column}, {Resource Name Column}, Resource@row, {Non-Client Work Column}, [Non - Client Work]@row)

Here are some Help Center articles I used to build this: SUMIFS function / @row Function / Cross Sheet References

Let me know if you have any questions about this!

Cheers,

Genevieve

Stevefc

Hi Genevieve,

Thank you for your answer, very helpful indeed. I will try out your suggestions and come back to you, thanks again,

Regards

Steve 

Genevieve P.

Hi Steve,

No problem at all! Happy to help.

Genevieve P.

Hi Steve,

If you're looking to build this in another sheet, you can actually just use a similar SUMIF formula, but use cross-sheet references instead of column references, and use SUMIFS (plural) instead, since you'll have more than one criteria to look for.

For example, if your Grid has the exact information as you've showed above, then in the very first Hrs cell, you can input a formula like this:

=SUMIFS({Hours - Client Column}, {Resource Name Column}, Resource@row, {Client Work Column}, [Client Work]@row)

You'll notice that for a SUMIFS plural, we first list the column we want to SUM, then we list the columns & criteria afterwards. This formula could then be drag-filled (see here) down the whole column in your chart and it will auto-populate based on what's in the Resource column and the Client Work column in your current chart.

To make the second formula for the non-client work, all you have to do is adjust the second column & second criteria:

=SUMIFS({Hours - Client Column}, {Resource Name Column}, Resource@row, {Non-Client Work Column}, [Non - Client Work]@row)

Here are some Help Center articles I used to build this: SUMIFS function / @row Function / Cross Sheet References

Let me know if you have any questions about this!

Cheers,

Genevieve

Stevefc

Hi Genevieve,

Thank you for your answer, very helpful indeed. I will try out your suggestions and come back to you, thanks again,

Regards

Steve 

Genevieve P.

Hi Steve,

No problem at all! Happy to help.

Stevefc

Hi Genevieve,

I have had a chance to try out the solution and it is working well, thank you again,  

Regards

Steve

Genevieve P.

Hi Steve,

I'm glad to hear it! Thanks for letting us know.