Sum a vlookup

[Chad]

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

 

[Marcelo]

hi Chad,

=sumproduct(--(a2:a100="Promo1")*(c2:c100))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Chad" escreveu:

 

[Chad]

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

 

[Harlan Grove]

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,B1:E1)))

 

[Chad]

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

 

[Harlan Grove]

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)))

 

[Chad]

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

 

[Bob Phillips]

=SUM((A2:A6=A8)*(INDEX(C2:F6,0,MATCH(B8,C1:F1))*(B2: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)

 

[Harlan Grove]

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)