SUMIFS for a 2-Dimensional Array

Nik Fuentes

Hi there,

I've got a sheet [Sheet A] that needs to summarize another sheet [Sheet B], where each row on Sheet A needs to reflect a column on Sheet B. I can do this very easily with a SUMIFS function.

e.g. Sheet A Row1 =SUMIFS({Sheet B column 3}, {Sheet B column 1}, "example", {Sheet B column 2}, true).

The issue with this setup is that I then have to define a new range reference for every single row [{Sheet B column 4} for Sheet A Row2, {Sheet B column 5} for Sheet A Row3, etc]. I'd much rather have a single reference [{Sheet B columns 1-X}] and just offset/column_index to the column I need. Any ideas on how to do that?

I don't think Smartsheets has any kind of offset() function like in Excel/Google Sheets. I don't think a VLOOKUP() or INDEX() can be used to return a range of values, either. That just leaves COLLECT(), which is optimized for checking columns vertically, not rows horizontally. The literature on COLLECT() doesn't make reference to 2-dimensional arrays anywhere.

I've somehow avoided having to build a solution like this for years now, so I'm a bit befuddled. I figured I'd ask the class before I start experimenting with COLLECT() by trial and error. Any insights y'all can offer?

Paul Newcome

AS tedious and time consuming as it is, your best option is to create the numerous references.

Paul Newcome

AS tedious and time consuming as it is, your best option is to create the numerous references.

Nik Fuentes

@Paul Newcome That was not the answer I was hoping to hear, but I appreciate the directness.

Paul Newcome

I just re-read your post and think I may have slightly misunderstood. Are you able to provide some screenshots for context?

Nik Fuentes

@Paul Newcome sure.

Sheet A looks like this. Each column looking at a different criterion for the SUMIFS() and each row is looking at a different range, which corresponds to a column on Sheet B. You can see {Jan Rev} here. The next row looks at {Feb Rev}, the next {Mar Rev}, etc. Instead of creating 20+ sheet references to handle all the rows, I'd ideally have 1 {Rev} reference that handles all the columns and switch which column we're looking at depending on the row. It'd be like setting the column index on an INDEX() function, where Row1 indexes 1 to get the Jan Rev column, Row2 indexes 2 to get the Feb Rev column, etc.

Is this more helpful?

Paul Newcome

Ok. Unfortunately that's how I initially thought it was, and I'll have to stick with my original comment.

Nik Fuentes

@Paul Newcome

(*sad trombone noises*)

Thanks for taking another look at it. This is ultimately what I was braced to do anyhow.