Sum of all values from a column X in a sheet A into a sheet B using index/match

Carlos Pallares

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?

Lucas Rayala

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.

Carlos Pallares

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!

Genevieve P.

Hi @Carlos Pallares

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