hlookup in sum function

[SuzyQ]

I have an array of values

Patching Crack Seal Chip Seal Overlay Rebuild Treatment
803.7 133.95 5036.52 21432 53580 Chip Seal
2160.99 360.165 13542.2 57626.4 144066 Crack Seal
76.53 12.755 479.588 2040.8 5102 Rebuild

Total maintenance cost =sum(???)

What I want is to look up the value of treatment in the specific row and use
the value in the sum function. In the example above the first row, Chip
Seal, would return 5036.52; the second row, Crack Seal, would return 360.165;
and the final row, Rebuild, would return 5102; these values would be used in
the calculation of the sum to give a total of $10498.69 How can I accomplish
this?

 

[T. Valko]

Try this...

Data in the range A1:F4

Enter this formula in G2 and copy down to G4:

=SUMIF(A$1:E$1,F2,A2:E2)

Then, for the grand total:

=SUM(G2:G4)

 

[Rik_UK]

Not sure it can be done in one step...

This is simple and will work, and as the table grows then drag down the
formula...

Assuming the table is in columns A to F then putting this in cell G2 and
dragging down will find the value of the required treatment in each row.

=HLOOKUP(F2,$A$1:$E$4,ROW(),0)

Then sum the formulas in column G with =SUM(G2:G4) giving 10498.69

 

[SuzyQ]

thanks - I did it this way and it worked fine. I didn't really want to have
the extra column, but I guess it's necessary. Thanks again!

 

[T. Valko]

I didn't really want to have the extra column,

but I guess it's necessary.

If you want a single formula...

A1:F1 = column headers
A2:E4 = numeric values
F2:F4 = treatment categories

=SUMPRODUCT(SUBTOTAL(9,OFFSET(A2,ROW(A2:E4)-ROW(A2),MATCH(F2:F4,A1:E1,0)-1)))