Multi-column spill range/dynamic arrays and SUMIFS

[Yvonne Love]

I'm getting more and more familiar with dynamic arrays formulas every day, and I am loving them! But what I haven't figured out how to do is use SUMIFS (or count/min/max, etc.) on a multi-column spilled range. For instance, I need to pull total hours for each company based on location. One company may have more than one location, and I used unique/filter/index to pull the unique company and location combinations. Now I need to sum the hours for each company/location combination, but since the spill range is two columns, I haven't be able to figure out how to do that and I haven't been able to find any blogs, videos, etc. that discuss that piece. I feel like it's something simple that I am just completely missing, but I wanted to ask the experts to see if anyone had any thoughts. I've attached sample data with 500 rows of data (and I used RANDARRAY to pull it!).

Alternatively, if this cannot be done, is there a way to pull two separate spill ranges using filter/unique, etc., where the company will spill as many times as there is a unique location (so could be only once or could be multiple times), and then the separate column pulling the unique location? I know I could use analysis formulas (sum, etc.) with two separate ones. I am just not thinking of the correct unique/filter arguments to pull the separate columns.

Thank you in advance for your assistance!
YL

 

[Peter Bartholomew]

You can use INDEX to separate the distinct locations and their company names to use as criteria in SUMIFS. Then, if you wish to recombine the columns into a single spilt range, you can use CHOOSE

= LET(
      RecordNum, SEQUENCE(ROWS(data)),
      CompanyLocation, INDEX(data,RecordNum,{3,1}),
      distinct, SORT(UNIQUE(CompanyLocation)),
      distinctCompany, INDEX(distinct,,1),
      distinctLocation,  INDEX(distinct,,2),
      hours, SUMIFS(
         data[HOURS],
         data[CLIENT NAME], distinctCompany,
         data[LOCATION], distinctLocation),
      CHOOSE({1,2,3}, distinctCompany, distinctLocation, hours )
   )

For reasons that may be obvious from the above, I recommend you use the LET function that removes the need to nest functions and provides some level of annotation in the names you select.

 

[p45cal]

This is what pivot tables are for; you don't need any formulae. See the table at cell I1 of the attached.
I've put a few formulae in column C as a cross check.

 

[Peter Bartholomew]

@p45cal
True, but I could equally say "This is just one thing that array formulas do well; you don't need any complicated special-purpose functionality"!
To be fair, I do turn to pivot tables if the use case requires data refresh from an external source, the data volume is large or I want the end-user to have the flexibility to slice and dice different data views. Conversely, I tend to avoid them if I want the output to respond instantly to changes within the input data set, I want to prevent the end-user from changing the data in view, I wish to perform further calculations based upon the intermediate table.

 

[Yvonne Love]

@Peter Bartholomew - thank you so much for that info! It is extremely helpful. I am still familiarizing myself with the LET formula, and it is very helpful to see the syntax!

@p45cal - thank you for your suggestion as well. In many ways, I love pivot tables, and in many ways I don't. The biggest drawback for me is how large the file size tends to be when I use them. And I also really like the immediacy of the array formulas for analysis and charting. But I definitely go back and forth depending on the situation, so I am glad to have both options!

Thank you both so very much!!!
YL