Forum Discussion

Copper Contributor

Mar 30, 2022

Solved

Create two lists

Hello, I am trying to figure out an automatic formula in excel to replace a pivot table. I have a table/array of data that includes multitude of information. From that raw data, I have a dro...

excel

Formulas and Functions

Macros and VBA

office 365

Lorenzo
Mar 31, 2022
Hi MichelleO

Not sure why I didn't think about this easier option first. A simple SUMIFS does it instead of the MAKEARRAY option I posted yesterday. With the same setup

in G5:

=SUMIFS(Table1[Qty], Table1[Kind],F5#, Table1[Name],G4#)
XL_DynamicSumifs_MichelleO.xlsx23 KB

Riny_van_Eekelen

Platinum Contributor

Mar 30, 2022

MichelleO Wondering why you would NOT want to use a pivot table for this. Probably doable but why reinvent the wheel? What Excel version are you using, by the way? And perhaps you can share a link to a file that contains a realistic example of what you raw data looks like.

MichelleO

Copper Contributor

Mar 30, 2022

Hi Riny, Because this is a shared document that a bunch of people go in and change/break the pivot, we're trying to get away from them if possible. I would normally live for Pivots, but trying to think of a new way to use. Raw data is extensive and in a table with multiple columns identifying each piece of this (can't share bc of proprietary info). Using Microsoft 365.

Starrysky1988
Iron Contributor
Mar 30, 2022
If the file can not be shared due to sensitive or privacy issue, we can only give you advice to use FILTER function as you are using Office 365. To my knowledge,using FILTER function shall be the best.