Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
I have searched around for an answer but have not found something that
specifially deals with my issue, so I apologize in advance if this was already answered. a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 I would like a function that sums the data associated with Promo1 during the week of 200702. Whatis the best way to do this? Thanks in advance, Chad |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
hi Chad,
=sumproduct(--(a2:a100="Promo1")*(c2:c100)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Chad" escreveu: I have searched around for an answer but have not found something that specifially deals with my issue, so I apologize in advance if this was already answered. a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 I would like a function that sums the data associated with Promo1 during the week of 200702. Whatis the best way to do this? Thanks in advance, Chad |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
Thanks Marcelo, however I am looking for a way to have the column reference
be dynamic...for example: a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 7 8 Promo1 200702 34 In cell C8, I would like to return the sum of whatever promo is entered into cell A8 under whichever week is entered into cell B8. Any suggestions? Thanks again, Chad "Marcelo" wrote: hi Chad, =sumproduct(--(a2:a100="Promo1")*(c2:c100)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Chad" escreveu: I have searched around for an answer but have not found something that specifially deals with my issue, so I apologize in advance if this was already answered. a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 I would like a function that sums the data associated with Promo1 during the week of 200702. Whatis the best way to do this? Thanks in advance, Chad |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
Chad wrote...
.... I would like a function that sums the data associated with Promo1 during the week of 200702. .... Given your sample table, if the week IDs would always be sorted in ascending order, =SUMIF(A2:A6,"Promo1",INDEX(B2:E6,0,MATCH(200702,B 1:E1))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
I found a formula that will take care of it...
=SumIf(a2:a6,a8,INDEX(b2:e6,,match(b8,b2:e2,0))) Thanks for everyone's help, Chad "Chad" wrote: Thanks Marcelo, however I am looking for a way to have the column reference be dynamic...for example: a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 7 8 Promo1 200702 34 In cell C8, I would like to return the sum of whatever promo is entered into cell A8 under whichever week is entered into cell B8. Any suggestions? Thanks again, Chad "Marcelo" wrote: hi Chad, =sumproduct(--(a2:a100="Promo1")*(c2:c100)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Chad" escreveu: I have searched around for an answer but have not found something that specifially deals with my issue, so I apologize in advance if this was already answered. a b c d e 1 200701 200702 200703 200704 2 Promo1 1 2 3 4 3 Promo2 5 6 7 8 4 Promo3 9 10 11 12 5 Promo1 13 14 15 16 6 Promo1 17 18 19 20 I would like a function that sums the data associated with Promo1 during the week of 200702. Whatis the best way to do this? Thanks in advance, Chad |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
Harlan Grove wrote...
.... =SUMIF(A2:A6,"Promo1",INDEX(B2:E6,0,MATCH(200702, B1:E1))) Didn't notice the entries in row 8. Make that =SUMIF(A2:A6,A8,INDEX(B2:E6,0,MATCH(B8,B1:E1))) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
Harlan,
Thanks for your answer. I ran into something else that I need your help on though. It is using the same information but requires a sumproduct formula. Let me show you. a b c d e f 1 200701 200702 200703 200704 2 Promo1 .02 1 2 3 4 3 Promo2 .01 5 6 7 8 4 Promo3 .03 9 10 11 12 5 Promo1 .12 13 14 15 16 6 Promo1 .07 17 18 19 20 7 8 Promo1 200701 13.19 Again, I would like the user to define which Promo and Week. However, instead of outputting a sum, I was wondering if it were possible to output the weighted average of all of the Promo1's in Week 200701 (which is just the sumproduct of the two/sum of the related values in column b) Thanks again for all of your help. Best, Chad "Harlan Grove" wrote: Harlan Grove wrote... .... =SUMIF(A2:A6,"Promo1",INDEX(B2:E6,0,MATCH(200702, B1:E1))) Didn't notice the entries in row 8. Make that =SUMIF(A2:A6,A8,INDEX(B2:E6,0,MATCH(B8,B1:E1))) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
=SUM((A2:A6=A8)*(INDEX(C2:F6,0,MATCH(B8,C1:F1))*(B 2:B6)))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Chad" wrote in message ... Harlan, Thanks for your answer. I ran into something else that I need your help on though. It is using the same information but requires a sumproduct formula. Let me show you. a b c d e f 1 200701 200702 200703 200704 2 Promo1 .02 1 2 3 4 3 Promo2 .01 5 6 7 8 4 Promo3 .03 9 10 11 12 5 Promo1 .12 13 14 15 16 6 Promo1 .07 17 18 19 20 7 8 Promo1 200701 13.19 Again, I would like the user to define which Promo and Week. However, instead of outputting a sum, I was wondering if it were possible to output the weighted average of all of the Promo1's in Week 200701 (which is just the sumproduct of the two/sum of the related values in column b) Thanks again for all of your help. Best, Chad "Harlan Grove" wrote: Harlan Grove wrote... .... =SUMIF(A2:A6,"Promo1",INDEX(B2:E6,0,MATCH(200702, B1:E1))) Didn't notice the entries in row 8. Make that =SUMIF(A2:A6,A8,INDEX(B2:E6,0,MATCH(B8,B1:E1))) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a vlookup
Chad wrote...
Thanks for your answer. I ran into something else that I need your help on though. It is using the same information but requires a sumproduct formula. Let me show you. a b c d e f 1 200701 200702 200703 200704 2 Promo1 .02 1 2 3 4 3 Promo2 .01 5 6 7 8 4 Promo3 .03 9 10 11 12 5 Promo1 .12 13 14 15 16 6 Promo1 .07 17 18 19 20 7 8 Promo1 200701 13.19 Again, I would like the user to define which Promo and Week. However, instead of outputting a sum, I was wondering if it were possible to output the weighted average of all of the Promo1's in Week 200701 (which is just the .... =SUMPRODUCT(--(A2:A6=A8),B2:B6,INDEX(C2:F6,0,MATCH(B8,C1:F1))) /SUMIF(A2:A6,A8,B2:B6) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |