Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Complex Formula/Lookup and total

Copper Contributor

I need assistance. I need to calculate the total number of man hours spent on multiple jobs from my file for the entire year to see which project(s) had the most hours sent. So I have multiple projects, each with multiple timesheet entries that I need to total at the bottom. Thoughts?

 

Screenshot 2024-02-05 095246.jpg

6 Replies

@stressbuny 

Presuming the first column is for 'Projects', all you really need is a pivot table to summarize.

 

Patrick2788_0-1707153976538.png

If you prefer a formula, you could use a dynamic array:

=LET(
    UniqueProjects, SORT(UNIQUE(Project)),
    totals, SUMIF(Project, UniqueProjects, ManHours),
    HSTACK(UniqueProjects, totals)
)

@Patrick2788 Thanks for the assistance. I am attaching my live file here. I have several tabs at the bottom I need to create the 'pivot table' for. I need one for SD's, DD's, CD's, and PR #s. I have never created a pivot table, so I don't know how to set that up based on the document you send. I don't know if I am linking this correctly, first time using this board. Thanks for your assistance!

 

 Excel File 

It appears you may have linked to a network share that I don't have access to view. You could host the file on OneDrive or send it to me via private message (if you're missing the attachment option when composing).
I sent via message as an attachment. It did not allow me to attach/upload a file through here. Thanks!
Your GROUPBY with the eta-reduced Lambda function worked perfectly.
Glad it worked! Not even tested as I don't have access to Insider's at work.