Sum of all values from a column X in a sheet A into a sheet B using index/match
Hi I have two sheets, one is Allocations and other is Rollup, Allocations has the following columns:
- Resource Name, I can several rows with the same value
- January 1
- January 2
- ....
- December 31
In Rollup I have the list of unique names listed in a Resource Name column, and also the same columns as Allocations. What I want to do in the Rollup sheet is that for Resource Name, Carlos Pallares, in January 1, to calculate the sum all the values for Carlos Pallares in January 1 from Allocation sheet.
The problem is that if I want to refer all columns at once to use a sumif for each column, I will exceed the cross-sheet limitation of 100, since I will have 365 columns, one for each day of the year. So I tried to use Index/Match since I can refer to all the columns and also use the index_column value to refer to any date column I have without using cross-sheet references but this only brings the first or last occurence of Carlos Pallares, but not the sum.
Is there anyway to use only the column_index as index formula does so I don't need to create the 100 cross-sheet references and I can sum all the values of a specific resource name for a specific column?
Answers
-
Hi @Carlos Pallares -- how many resource names do you have? If it's not that many, then I would instead suggest flipping your columns and rows so that you summarize each name as a column and the days as rows. This might help with the data load.
-
Thanks for the quick reply Lucas,
What I follow is that, now I can use a SUMIF where Resource Name coming from the source sheet is a reference too, but the columns, the days mentioned from January 1 to December 31, they will be references too. Can you help me to understand a little more your idea?
Because I can't change the source sheet structure.
Thanks!
-
With the SUMIFS function you will need to reference the column individually. I would suggest creating 12 sheets: one for each month. Then you can have 31 columns (or fewer, depending on the Month) per sheet, so you won't exceed the cross-sheet reference limit.
If you need to see the entire year in one go, you could have a TOTAL column in each sheet that sums the data across the row. Then create a Report with all 12 sheets showing that Total column, then Group by each individual. Would that work for you?
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!