• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Summary of ranges in a dynamic table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Summary of ranges in a dynamic table|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Summary of ranges in a dynamic tabl…
sp_PrintTopic sp_TopicIcon
Summary of ranges in a dynamic table
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
1
July 17, 2022 - 5:47 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
Alan Sidman
Steamboat Springs, CO
Member
Members


Trusted Members
Level 0
Forum Posts: 132
Member Since:
October 18, 2018
sp_UserOfflineSmall Offline
2
July 17, 2022 - 9:32 am
sp_Permalink sp_Print

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"
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
3
July 17, 2022 - 1:39 pm
sp_Permalink sp_Print

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

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
July 17, 2022 - 2:18 pm
sp_Permalink sp_Print

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.

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
5
July 17, 2022 - 2:36 pm
sp_Permalink sp_Print

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!!!!!!!!!!

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
July 17, 2022 - 4:31 pm
sp_Permalink sp_Print sp_EditHistory

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

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
7
July 17, 2022 - 4:42 pm
sp_Permalink sp_Print

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]]

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
8
July 17, 2022 - 4:49 pm
sp_Permalink sp_Print sp_EditHistory

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. 

sp_AnswersTopicAnswer
Answers Post
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
9
July 18, 2022 - 11:48 pm
sp_Permalink sp_Print

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

Avatar
Jessica Stewart
Northern USA
Member
Members


Trusted Members
Level 0
Forum Posts: 204
Member Since:
February 13, 2021
sp_UserOfflineSmall Offline
10
July 19, 2022 - 2:03 am
sp_Permalink sp_Print

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.

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
11
July 19, 2022 - 4:48 am
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
12
July 19, 2022 - 6:14 pm
sp_Permalink sp_Print

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!!!!!!!!!!!!!

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 442
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
13
July 19, 2022 - 7:49 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
14
July 19, 2022 - 8:52 pm
sp_Permalink sp_Print sp_EditHistory
Great, thanks!!!!!!!!!!
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Velouria
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6222
Posts: 27291

 

Member Stats:
Guest Posters: 49
Members: 31913
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.