| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup addition
I am using the following to add together the two categories. How can I
change it to still give me a sum even if one of the categories is missing? Example: The June report shows a category of Sales & Logistics but doesn't even mention Accounting. Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. Is this possible? Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FALSE))+VLOOKUP("Logistics" ,$G$1:$H$205,2,FALSE) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
You could do it like this:
=IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, VLOOKUP("Sales",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47*pm, nt wrote: I am using the following to add together the two categories. *How can I change it to still give me a sum even if one of the categories is missing? * Example: *The June report shows a category of Sales & Logistics but doesn't even mention Accounting. *Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. *Is this possible? *Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FA*LSE))+VLOOKUP("Logistics ",$G$1:$H$205,2,FALSE) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
Works perfectly! Thank you so much - you are a lifesaver! One question: do
you see a problem with this formula if I had to nest or include 15 different look ups? "Pete_UK" wrote: You could do it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, VLOOKUP("Sales",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47 pm, nt wrote: I am using the following to add together the two categories. How can I change it to still give me a sum even if one of the categories is missing? Example: The June report shows a category of Sales & Logistics but doesn't even mention Accounting. Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. Is this possible? Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FAÂ*LSE))+VLOOKUP("Logistic s",$G$1:$H$205,2,FALSE) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
You're welcome.
As for your follow-on question, you might find it easier to use something like this: =SUMPRODUCT(--($G$1:$G$205={"Accounting","Sales","Logistics"}),$ H$1:$H $205) Then you could add further categories inside the curly braces, separated by a comma, as shown with the three already there. Hope this helps. Pete On Jan 21, 4:45*pm, nt wrote: Works perfectly! *Thank you so much - you are a lifesaver! *One question: *do you see a problem with this formula if I had to nest or include 15 different look ups? "Pete_UK" wrote: You could do it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Accounting",$G$1:$H$205,2,0)) * +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Sales",$G$1:$H$205,2,0)) * *+IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47 pm, nt wrote: I am using the following to add together the two categories. *How can I change it to still give me a sum even if one of the categories is missing? * Example: *The June report shows a category of Sales & Logistics but doesn't even mention Accounting. *Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. *Is this possible? *Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FA**LSE))+VLOOKUP("Logistic s",$G$1:$H$205,2,FALSE)- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
If you have up to 15 variables it'd be better to list those variables in a
range of cells then use a formula like this: =SUMPRODUCT(--(ISNUMBER(MATCH(G1:G100,A1:A15,0))),H1:H100) Where A1:A15 are the list of variables. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You're welcome. As for your follow-on question, you might find it easier to use something like this: =SUMPRODUCT(--($G$1:$G$205={"Accounting","Sales","Logistics"}),$ H$1:$H $205) Then you could add further categories inside the curly braces, separated by a comma, as shown with the three already there. Hope this helps. Pete On Jan 21, 4:45 pm, nt wrote: Works perfectly! Thank you so much - you are a lifesaver! One question: do you see a problem with this formula if I had to nest or include 15 different look ups? "Pete_UK" wrote: You could do it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, VLOOKUP("Sales",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47 pm, nt wrote: I am using the following to add together the two categories. How can I change it to still give me a sum even if one of the categories is missing? Example: The June report shows a category of Sales & Logistics but doesn't even mention Accounting. Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. Is this possible? Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FA**LSE))+VLOOKUP("Logistic s",$G$1:$H$205,2,FALSE)- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
I must be doing something wrong - the vlookup works perfectly but the
sumproduct is returning the #value! error - also, remember that not all of the categories will appear every month (isna*). I love the sumproduct idea - what am I doing wrong and can it calculate a sum if one of the items is not there? "Pete_UK" wrote: You're welcome. As for your follow-on question, you might find it easier to use something like this: =SUMPRODUCT(--($G$1:$G$205={"Accounting","Sales","Logistics"}),$ H$1:$H $205) Then you could add further categories inside the curly braces, separated by a comma, as shown with the three already there. Hope this helps. Pete On Jan 21, 4:45 pm, nt wrote: Works perfectly! Thank you so much - you are a lifesaver! One question: do you see a problem with this formula if I had to nest or include 15 different look ups? "Pete_UK" wrote: You could do it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, VLOOKUP("Sales",$G$1:$H$205,2,0)) +IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47 pm, nt wrote: I am using the following to add together the two categories. How can I change it to still give me a sum even if one of the categories is missing? Example: The June report shows a category of Sales & Logistics but doesn't even mention Accounting. Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. Is this possible? Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FAÂ*Â*LSE))+VLOOKUP("Logist ics",$G$1:$H$205,2,FALSE)- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup addition
What both SP formulae are saying is if the cells in column G are equal
to Accounting OR Sales OR Logistics (etc) then add the corresponding cell from column H. The main difference between my suggestion and Biff's is that in mine the variables are listed within the formula (and so it would need to be amended if you wanted to change some values), whereas Biff's solution uses a table to list the variables, and so you only need to amend the table rather than the formula - however, they are functionally the same. If you are getting a #VALUE error then you might have arrays which are of different lengths (they should be the same size), or your data may be text rather than numbers. Post the exact formula(s) that you used, so that we might spot your error more specifically. Hope this helps. Pete On Jan 21, 6:46*pm, nt wrote: I must be doing something wrong - the vlookup works perfectly but the sumproduct is returning the #value! error - also, remember that not all of the categories will appear every month (isna*). *I love the sumproduct idea - what am I doing wrong and can it calculate a sum if one of the items is not there? "Pete_UK" wrote: You're welcome. As for your follow-on question, you might find it easier to use something like this: =SUMPRODUCT(--($G$1:$G$205={"Accounting","Sales","Logistics"}),$ H$1:$H $205) Then you could add further categories inside the curly braces, separated by a comma, as shown with the three already there. Hope this helps. Pete On Jan 21, 4:45 pm, nt wrote: Works perfectly! *Thank you so much - you are a lifesaver! *One question: *do you see a problem with this formula if I had to nest or include 15 different look ups? "Pete_UK" wrote: You could do it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, VLOOKUP("Accounting", $G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Sales",$G$1:$H$ 205,2,0)),0,(VLOOKUP ("Sales",$G$1:$H$205,2,0))+IF(ISNA(VLOOKUP("Logist ics",$G$1:$H $205,2,0)),0,VLOOKUP("Logistics",$G$1:$H$205,2,0)) All one formula, so be wary of spurious line-breaks in the newsgroups. It might be easier to see what is happening if I post it like this: =IF(ISNA(VLOOKUP("Accounting",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Accounting",$G$1:$H$205,2,0)) * +IF(ISNA(VLOOKUP("Sales",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Sales",$G$1:$H$205,2,0)) * *+IF(ISNA(VLOOKUP("Logistics",$G$1:$H$205,2,0)),0, * * * VLOOKUP("Logistics",$G$1:$H$205,2,0)) i.e. return zero instead of an error for each of the categories. Hope this helps. Pete On Jan 21, 2:47 pm, nt wrote: I am using the following to add together the two categories. *How can I change it to still give me a sum even if one of the categories is missing? * Example: *The June report shows a category of Sales & Logistics but doesn't even mention Accounting. *Using this formula gives me an error although I still need to have an amount even if the Accounting category is missing. *Is this possible? *Thanks in advance!!! =VLOOKUP("Accounting",$G$1:$H$205,2,FALSE)+VLOOKUP ("Sales",$G$1:$H$205,2,FA***LSE))+VLOOKUP("Logisti cs",$G$1:$H$205,2,FALSE)- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Addition in Excel | Excel Discussion (Misc queries) | |||
| Subtraction/addition | Excel Discussion (Misc queries) | |||
| Addition? | Excel Discussion (Misc queries) | |||
| addition | New Users to Excel | |||
| row addition | Excel Worksheet Functions | |||
Linear Mode
