Combining a COUNTIF, a SUMIF and some operations into a single dynamic formula

[Eddie505]

Hello,

I'm supposed to be the Excel wizard at work, so when someone asked me for help I swaggered over with my usual confidence, only to be totally stumped. Please could someone help me simplify an overly-complicated sequence of calculations into something more tidy and dynamic?

I created the attached spreadsheet to explain what the current solution is in a simplified manner. I annotated it to explain what I would like to do. Basically, its a spreadsheet that allows workers to write down which projects they've been working on, and the output is a breakdown of resources that have been added to each project.

If it's too much to ask for a finished solution, would anyone be willing to point me into the right direction? I had a feeling that maybe array formulas could help me, but I've now learned all about them and still can't quite come up with a nice simple solution.

Many thanks for any help you can offer!

I can't seem to work out how to upload a worksheet, so just going to share it via a OneDrive link:

Combining operations for worker project allocations.xlsx

Best,

Eddie

[#2 · Accepted solution]

@Eddie505 One option would be with Power Query and requires a "Refresh" when Table1 gets updated. See attached.

A formula-only solution (without helpers etc.) would require  a quite complex approach which I haven't thought about yet.

[#3]

@Riny_van_Eekelen thanks a lot for this. I've never used power queries before, so this looks like a good opportunity to learn!

[#4]

@Eddie505 This would be a good point to start.

https://exceloffthegrid.com/power-query-introduction/ 

[#5]

@Riny_van_Eekelen 

Hi Riny. I loved the tone of the tutorial you recommended, and I totally agree. It really is an amazing tool! I've been able to do everything I wanted to using that tutorial and your example, including filtering results with an input parameter. Really looking forward to using this more in future.

There is one thing I'm struggling with though. The column headers are created as text, not dates. See screenshot below:

If they were formatted as dates, then that would allow any subsequent charts created from this data to recognise when there's an "empty" month, and create a zero value for that month on the chart. As it is now, the Excel chart simply skips that month. E.g.: in my demo sheet, this is what happens if I don't fill in any projects for March.

I suppose getting the date formatting right would be one way to solve this, but then I suppose an alternative solution might be to create a comprehensive list of months, and then merge the data within the Power Query tool with this list? In that way, even if Excel continues to treat the column headers as text, then at least there'd still be a column for every single month, regardless of whether its blank or not. On the down-side, I was really enjoying how the first approach automatically resized the table to fit the max and min dates, and going in this direction would presumably complicate that.

So I just thought to share that with you in case you had any thoughts and/or recommendations!

[#6]

@Eddie505 Not fully up to speed with your achievements, but am glad you discovered the power of Power Query.

Can say that headers in structured tables are by default texts. So, you can't transform them to dates unfortunately.