lookup more than one value across a table

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

i have a spreadsheet with 52 rows of data relating to properties, i.e. 52
properties. in column B i have the information to which office location it
belongs (10 different offices). in column H i have the price of the
property. i now would like to summarise a total for each office.

i tried to use the following formula
=OFFSET(A1,MATCH("Nottingham",B2:B52,0),7)
but it only returns the first value matching the criteria and doesn't add up
the other 4 belonging to this office.

how can i extend/amend the formula to either show all results in a list or
add them up already?

Many thanks
 
thanks jacob, that worked.

another question:
what if i want to add the values of let's say column H and K (as in K i've
got the sales fees for each property)?

txs
 
thanks jacob, that worked.

another question:
what if i want to add the values of let's say column H and K (as in K i've
got the sales fees for each property)?

txs
 
If you have multiple columns to be summed use the below formula. Please note
that this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

=SUM(IF($B$1:$B$52="Nottingham",$H$1:$H$52+$K$1:$K$52))

If this post helps click Yes
 
If you have multiple columns to be summed use the below formula. Please note
that this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

=SUM(IF($B$1:$B$52="Nottingham",$H$1:$H$52+$K$1:$K$52))

If this post helps click Yes
 
Back
Top