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?

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.