Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
I'm wanting to lookup up one value in a list of other values and return the
sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
=sumif(G2:G20, N1, H2:H20)
-- HTH... Jim Thomlinson "lightbulb" wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
To copy this down, you can use:
=sumif(G$2:G$20, N1, H$2:H$20) Regards, Fred "Jim Thomlinson" wrote in message ... =sumif(G2:G20, N1, H2:H20) -- HTH... Jim Thomlinson "lightbulb" wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
=SUMPRODUCT((G1:G200=N1)*H1:H200)
Adjust the ranges in columns G and H as required and copy down this formula in column O "lightbulb" wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
Hi
I assume your values start in row 2 so in O2 enter =sumproduct(--(N2=$G$2:$G$100),$H$2:$H$100) Copy formula down change the range to fit your needs but remember the range has to be exactly the same in both sides of the formula "lightbulb" wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
You could try this in O1:
=SUMIF(G:G,N1,H:H) then copy down to cover the items you have in column N. Hope this helps. Pete On Jun 17, 2:19*pm, lightbulb wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. *I want the sum from column H each time the item in Column N appears in Column G. *So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G * * * Column H Allen * * * * * * * * * 45 ABA * * * * * * * * * *113 A&B * * * * * * * * * *75 A&B * * * * * * * * * *65 A&B * * * * * * * * * *235 Accu * * * * * * * * * 117 Accu * * * * * * * * * 85 Ahaus * * * * * * * *10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum Lookup
Hi!
Please use: =SUMIF($G$1:$G$8,N1,$H$1:$H$8) I'm sure it will help. Regards "lightbulb" wrote: I'm wanting to lookup up one value in a list of other values and return the sum of the values in the column next to it but can't figure out how. For example, I have a list of items in column N. I want the sum from column H each time the item in Column N appears in Column G. So basically anytime the item in Column N appears in Column G, I want it to sum each value in Column H in the corresponding (same row that the data appeared in in column G Column G Column H Allen 45 ABA 113 A&B 75 A&B 65 A&B 235 Accu 117 Accu 85 Ahaus 10 Say I have A&B in column N (I'm working in column O)....I want to know the sum for A&B...so it should return 375. Any help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |