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

  • Lucas Rayala
    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
    edited 01/12/24

    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!

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!