Sum a vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chad - 16 Aug 2006 20:00 GMT

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

Reply to this Message

Marcelo - 16 Aug 2006 20:11 GMT

hi Chad,

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

hth

Signature

regards from Brazil
Thanks in advance for your feedback.
Marcelo

> 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
[quoted text clipped - 15 lines]
> Thanks in advance,
> Chad

Reply to this Message

Chad - 16 Aug 2006 20:17 GMT

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

> hi Chad,
>
[quoted text clipped - 21 lines]
> > Thanks in advance,
> > Chad

Reply to this Message

Chad - 16 Aug 2006 20:29 GMT

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

> Thanks Marcelo, however I am looking for a way to have the column reference
> be dynamic...for example:
[quoted text clipped - 40 lines]
> > > Thanks in advance,
> > > Chad

Reply to this Message

Harlan Grove - 16 Aug 2006 20:27 GMT

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

Reply to this Message

Harlan Grove - 16 Aug 2006 20:29 GMT

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

Reply to this Message

Chad - 16 Aug 2006 22:03 GMT

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...
> ....
[quoted text clipped - 3 lines]
>
> =SUMIF(A2:A6,A8,INDEX(B2:E6,0,MATCH(B8,B1:E1)))

Reply to this Message

Bob Phillips - 16 Aug 2006 22:47 GMT

=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.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Harlan,
> Thanks for your answer. I ran into something else that I need your
[quoted text clipped - 27 lines]
> >
> > =SUMIF(A2:A6,A8,INDEX(B2:E6,0,MATCH(B8,B1:E1)))

Reply to this Message

Harlan Grove - 16 Aug 2006 23:03 GMT

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
[quoted text clipped - 13 lines]
>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 to this Message