| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup Value in Table | Excel Worksheet Functions | |||
| Table lookup | Excel Worksheet Functions | |||
| Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
| lookup a value in a table | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
