hlookup in sum function
 

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?

hlookup in sum function
 

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)

--
Biff
Microsoft Excel MVP


"SuzyQ" wrote in message
...
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?

hlookup in sum function
 

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


--
Kind regards

Rik


"SuzyQ" wrote:

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?

hlookup in sum function
 

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!

"Rik_UK" wrote:

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


--
Kind regards

Rik


"SuzyQ" wrote:

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?

hlookup in sum function
 

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)))

--
Biff
Microsoft Excel MVP


"SuzyQ" wrote in message
...
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!

"Rik_UK" wrote:

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


--
Kind regards

Rik


"SuzyQ" wrote:

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?