How to Sum Rows within a named range based on text criteria

**benmoore** · 07-15-2019, 09:34 AM

I have a few named ranges with different costs broken down by month. I need a macro to take all the repeated titles (ex. travel) within the named range and sum them into one row, and repeat this for each named range. Basically consolidating the data, sorry for the funky table thats just for reference the splits between named ranges wont exist all the data is together.

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec FY 2019
(first named range)
Travel 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Travel 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Travel 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Training 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Telephone 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Outside Serv 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Indirect Labor 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Fringe 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
(different named range)
Travel 1,000.00 500.00 300.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,700.00
Travel 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Training 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Supplies and Equipment 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Outside Services 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Other Employee Related Costs 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Other Employee Related Costs 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00
Other 1,000.00 500.00 75.00 900 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 2,475.00

**Arkadi** · 07-15-2019, 10:44 AM

Hi, welcome to the forum.

You will get much better help if you provide a workbook... nobody wants to have to guess at replicating your setup to test.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**benmoore** · 07-15-2019, 10:59 AM

Included a worksheet example just need to sum the data in the named ranges with the same title (ex.Travel) into one row from each named range. Thanks

**Arkadi** · 07-15-2019, 11:18 AM

I don't see a named range, but I assume your data is an example of a named range's contents? Where would the output go? New sheet? New area? Should the program make the new sheet or will you have a sheet already?

**benmoore** · 07-15-2019, 11:26 AM

Wherever is easiest to output, creating a new sheet would suffice. The data is sorted in each named range, so a new named range begins with a section of travel rows. Ex. Named range 1 would be rows 1:22, then the next would be 23:40 and so on.