Need help distilling a list of data by criteria... dynamic data sets.

[mjm6]

Folks, I have a problem that I am struggling to resolve.

I have a list that is dynamic (the length of the list can change, so I have to accommodate the possibility that it grows or shrinks. I have that working properly, and it is properly populating the cells with data.

I have been asked to take that list and distill it down based on criteria in the list. For example, the list has headers like this:

Country State City Year {additional columns of data}

What I need to do is generate a new list that focuses on a specific set of criteria, and combines the values of the rows that have the same criteria.

For example,
USA California Los Angeles 2020 {additional data in columns}
USA California Los Angeles 2020 {additional data in columns}
USA California Los Angeles 2022 {additional data in columns}
USA California Los Angeles 2024 {additional data in columns}
USA California Los Angeles 2020 {additional data in columns}
USA California Los Angeles 2024 {additional data in columns}
USA California Los Angeles 2026 {additional data in columns}

The idea is to take the array above and sum all the columns to the right of the date, but do it based on the the values of the first four columns matching. So the final array will look like this:
USA California Los Angeles 2020 {SUM OF additional data in columns}
USA California Los Angeles 2022 {SUM OF additional data in columns}
USA California Los Angeles 2024 {SUM OF additional data in columns}
USA California Los Angeles 2026 {SUM OF additional data in columns}

The rows that have all four columns on the left matching will be summed together, and the ones that are unique will still be there. So the new array will be smaller and will combine the individual lines that have the same test criteria. Since the starting data set is dynamic, this one will also be dynamic, and as long as there is some overlapping data, the new array will have fewer rows. Also, I do not want any duplicates. This has to be done in formulas, not through VBA.

I don't care about the order of the rows, so I thought I would start by looking at the first row and then doing a SUMIF of the rest of the list. That would work for the first row, but after that, I have to check to make sure I haven't done the same criteria already and skip down to the next row of source info if I have. I'm not sure I can do this without some VBA...

Suggestions on how to tackle this in a single cell formula?

Thanks,

---Mjm6

[FDibbins]

Hi, welcome to the forum

Probably need to use COUNTIFS?

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.