Log In
Register
Home
March 10, 2016
Offline
Hi,
Attached is the file with the problem I have in the formula
The Project issue has a formula in the yellow cells that does not work
In the formula I need it to summarize the cells of a particular project - on certain dates - when the range of dates changes
I set up in the file but it does not work - I would love to get an answer.
Also, is it possible to set a date in the outer cell and it will automatically summarize the cells from the first date to the date I wrote in the outer cell? That is, each time there is a change in the summary range
Thank you very much for the answer !!!
Leah
See Answer
Trusted Members
October 18, 2018
Offline
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"
March 10, 2016
Offline
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
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
March 10, 2016
Offline
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!!!!!!!!!!
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
See attached. Please note that I still used the columns that you mentioned earlier. Changed the header to Delivery to test the outcome.
March 10, 2016
Offline
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]]
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
Answers Post
March 10, 2016
Offline
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
Trusted Members
February 13, 2021
Offline
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.
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
March 10, 2016
Offline
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!!!!!!!!!!!!!
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
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.
1 Guest(s)
