Forum Discussion

Connor McGuigan's avatar
Connor McGuigan
Copper Contributor
Oct 30, 2018

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 Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    people here at the forum can help you better if you attach a non-sensitive file.
    thanks

  • Connor McGuigan's avatar
    Connor McGuigan
    Copper 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. 

Resources