0

I'm a Excel novice. Forgive my naivety but I cannot find the answer to this.

I have a master list of roadway segments and need to find the sum of all the unique combinations of segments based on number of lanes (2,3,4,5,6 or 8) and width of the roadway (50,55,60,80,100,106,108,110,120,124,126,140,160,180,200,220,or 240). The segments lengths are in miles.

For example: how many roadway miles are there that are 2 lanes and 50' wide?

Table is set up like this:

Image

Improve this question

1 Answer 1

Reset to default
0

I think you want the SUMIFS function. It will sum up the values in a given range that meet any number (>= 1) of conditions you assign.

 SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

In your case, assuming that column S is what you want to sum, this formula should work:

=SUMIFS(S:S,Q:Q,2,L:L,50)

I don't find this to be the easiest formula to remember or understand when I look at it. One thing that helps me understand it when needing to change it later is to use named ranges. Then all you would need to do is update the values in the search cells without needing to figure out the formula again.

=SUMIFS(SEGMENT_LENGTH_DATA,LANE_COUNT_DATA,SEARCH_LANES,ROAD_WIDTH_DATA,SEARCH_WIDTH)

See the Office Support SUMIFS documentation for more information on syntax (e.g. wildcard support) and troubleshooting tips.

Improve this answer
2
  • I appreciate the break down. This helped me understand the logic behind the formula. Now I understand how it works and how useful it can be. Thank you!
    – ad1990
    Commented May 8, 2017 at 18:50
  • Glad I could help. If you could eventually accept an answer that would be appreciated!
    – ojchase
    Commented May 9, 2017 at 16:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .