lookup more than one value across a table
 

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

lookup more than one value across a table
 

Total for nottingham

=SUMIF($B$1:$B$52,"Nottingham",$H$1:$H$52)
--
If this post helps click Yes
---------------
Jacob Skaria


"Dee" wrote:

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

lookup more than one value across a table
 

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



"Jacob Skaria" wrote:

Total for nottingham

=SUMIF($B$1:$B$52,"Nottingham",$H$1:$H$52)
--
If this post helps click Yes
---------------
Jacob Skaria


"Dee" wrote:

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

lookup more than one value across a table
 

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
---------------
Jacob Skaria


"Dee" wrote:

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



"Jacob Skaria" wrote:

Total for nottingham

=SUMIF($B$1:$B$52,"Nottingham",$H$1:$H$52)
--
If this post helps click Yes
---------------
Jacob Skaria


"Dee" wrote:

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

lookup more than one value across a table
 

Many thanks, just what I was looking for.
you saved me hours of fiddling about.


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Dee" wrote:

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



"Jacob Skaria" wrote:

Total for nottingham

=SUMIF($B$1:$B$52,"Nottingham",$H$1:$H$52)
--
If this post helps click Yes
---------------
Jacob Skaria


"Dee" wrote:

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