Forum Discussion
Using named ranges to sum up values throughout a worksheet?
I have a workbook that has information on vehicles. Each vehicle has its own row, and the A column includes a serial number for each vehicle. Columns G and H have values for each vehicle (gallons and mileage) that I want to sum up.
I have organized all the serial numbers that appear in the workbook into a series of named ranges. Each named range is basically a group of vehicles, organized by serial number. For each sheet, I hope to get the total gallons (G column) and mileage (H column) associated with each named range.
I need two formulas:
1) One that, for each named range, locates every cell in the A column that contains one of the range's values, then sums up all the corresponding (on the same row) cells in the G column.
1) One that does the same, but sums up corresponding values in the H column.
Let me know if you can help me with this. I can attach the workbook if needed.
- Lorenzo KimBronze Contributorpeople here at the forum can help you better if you attach a non-sensitive file.
thanks - Connor McGuiganCopper Contributor
Looks like I'll need a Sumif or Sumifs function. My range is A2:A1156 and my sum_range is G2:G1156. I don't know how to write the criteria. It should amount to: "if cell in column A contains any one of the values specified in NAMED RANGE."
I'm looking for partial matches. Column A has text along with serial numbers, so a cell in column A meets criteria if it has the NAMED RANGE value anywhere in its text.