Lea

If you normalize your data as I have done in the attached by unpivotting the data, then you can apply Excel formulas like Sumifs or pivot your data to include certain dates

I used the following Mcode in Power Query to "normalize" your data

let
Source = Excel.CurrentWorkbook(){[Name="Table_assignments"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"1", "Primary Skills"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Employee", "Org", "Project Assigment", "Actual", "Planned"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Attribute", null}}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Attribute] <> null))
in
#"Filtered Rows"

I knew about the Unpivot solution which is indeed a good solution.
The problem is I taught it to my girlfriend
And her manager did not want this solution -
Because as soon as you enter data in the lateral table - it is not automatically updated in the longitudinal table
- you have to click on Refresh, and he is afraid he will be missed.
Is it possible to set it to update automatically every time we enter a data ??

And the original structure of the table was important to him
That is why a solution with Sumifs should be introduced here
Who knew each time to take a different range of dates.

Thanks for the response!!
Leah

As an alternative you could us a formula like this:

=SUM(Table_assignments[[23-May-2022]:[4-Jul-2022]]*--(Table_assignments[Project Assigment]=B2))

.... but I agree that it would be better to flatten/unpivot the data first, as Alan suggested.

I need at this point to use with the formula you wrote
I'm copying it to a file and it's causing a glitch -
Can you please write it to me inside the file thanks !!

I will note that the formula should summarize dates according to the Project and also according to the ORG -
(I marked them in red text in the table)
Therefore should be used with Sumifs

Thank you!!!!!!!!!!

See attached. Please note that I still used the columns that you mentioned earlier. Changed the header to Delivery to test the outcome.

Amazing solution !!!
I would like to understand what the sign means * -
And is it possible that the end date will be written in an external cell and I will only have to write another date there?
Instead of going into the formula and changing in this part-: [4-Jul-2022]]

Changed the formula so that it will except an end date based on a text entered in another cell, but I don't consider this a good solution as the "date headers" in your Table 1 are inconsistent. I would still go for a more stable approach that flattens the data before you start summarizing/filtering it. But that's your choice.

The formula has three elements. The first is an array (the columns falling in the data range) you want to analyze. That array is than multiplied by two more arrays. One based on where the Project Assignment equals the Project Name (column B). The other where Org equals the header in D1.

These last two arrays return TRUE of FALSE. Wrapping it brackets and preceded by a double minus -- turns them to either 1 or 0. Did that out of habit but realized you don't need it. 

I turned on the amazing function!
I pulled the formula to the right to get more answers
And it displays answer 0
Attached file The problem exists in column E
Thanks for the help, Leah

I would agree with the unpivoting solution Alan provided. I understand your boss's leeriness at remembering to click refresh as I make worksheets for my co-workers and also worry they will come to me a million times with a refresh question (plus I make it a habit to lock my worksheets further complicating the situation). The solution I came up with was to write a quick VBA code to unlock my sheet and refresh the table; I will tie the code to an event, either worksheet change or activate/deactivate event so it works while the end user is working in the worksheet in their natural flow. This way they don't have to remember and I can lock my sheet so they can't accidentally mess with my formulas.

Hi Lea,

I had a look at your file and note that you just dragged the formula across. Unfortunately, that doesn't work as you might expect when you use structured table references. I didn't go "all-the-way" to make the references absolute in the attached file as it was quite easy to just replicate the formula in the next cell and just change the last reference from D1 to E1.

More about absolute table references in the link below:

Excel Table Absolute Structured References

Please see attached.

Hi,
I ran the formula on my file and came across a REF error message
Attached File
The Project issue has a fixed date and a variable date - they are highlighted in different colors with a legend on the side
In cell Z7 I could not run the formula.
I would appreciate help correcting the formula
Thank you!!!!!!!!!!!!!

In W3, you typed 11-Jul-2022 as a real date, but the header in the assignments table has a text "7/11/2022". Hence, you are referencing a non existing column name.

Edit: Moreover, the first column name is "23-May-2022". Not "23-May-22".

The correct formula should be:

=SUM(INDIRECT("Table47[[23-May-2022]:["&$W$3&"]]")*(Table47[Project Assigment]=$B8))

with "7/11/2022" in W3.

Great, thanks!!!!!!!!!!