Sorted appearing table (vlookup, if, concontinate)???
 

I currently have spreadsheets on which things appear only if certain criteria
are met (call it €œOrdersheet€�). I know this uses the If/Concatenate function.
most of it is working properly, but I recently was asked to add a section to
it, and I'm not sure how to make it work.

On another spreadsheet (same workbook) I have a sheet with prizes (well
call it €œprizesheet€�). each of the employee's put down what they wanted if
they met certain incentive goals. they were given a list with slots ranging
1-14. Each person was told to order desired prizes in these slots. Some
people put multiples of the same items on the sheet, and in addition the
number they wanted to buy, (also some items are only available in certain
slots).
I need it to appear on the order form with only the totals for each type,
not the goal at which they were achieved.

for example if a person choose on the Prizesheets:
level prize
1 watch 1
1 or bracelet 0
2 notebook 1
3 mug 1
4 watch 1
5 mug 1
and in addition chose to purchase
watch 2
notebook 1

then I need it to show on my Ordersheet as
watch 4
notebook 2
mug 2


in addition, I need it to keep updating based on the number of goals they
meet,
so that if they meet only 3 goals then only the first 3, plus purchases is
totaled, but if they meet 5 goals, then those additional goals get added in
at that time. (hope that makes sense). please help, or point me in the right
direction, if you can. thanks

Sorted appearing table (vlookup, if, concontinate)???
 

Let's say level is in A1:A8, item description is in B1:B8, and amount is in
C1:C8. Also, assuming that for purchased items, the level number in column A
is either empty or 0.

The level attained is in F1, item description you want totaled is in G1,
try this formula in H1:

=SUMPRODUCT(--($A$1:$A$8<=F1),--($B$1:$B$8=G1),$C$1:$C$8)

Adjust ranges to suit, but prior to XL2007, sumproduct cannot use an entire
column (eg A:A).

It's not clear from your data if the item description and amount are in
separate columns (looks like they may be concatenated together in the same
cell). I would split these - I think it would make it easier to work w/the
data.


"Luna" wrote:

I currently have spreadsheets on which things appear only if certain criteria
are met (call it €œOrdersheet€�). I know this uses the If/Concatenate function.
most of it is working properly, but I recently was asked to add a section to
it, and I'm not sure how to make it work.

On another spreadsheet (same workbook) I have a sheet with prizes (well
call it €œprizesheet€�). each of the employee's put down what they wanted if
they met certain incentive goals. they were given a list with slots ranging
1-14. Each person was told to order desired prizes in these slots. Some
people put multiples of the same items on the sheet, and in addition the
number they wanted to buy, (also some items are only available in certain
slots).
I need it to appear on the order form with only the totals for each type,
not the goal at which they were achieved.

for example if a person choose on the Prizesheets:
level prize
1 watch 1
1 or bracelet 0
2 notebook 1
3 mug 1
4 watch 1
5 mug 1
and in addition chose to purchase
watch 2
notebook 1

then I need it to show on my Ordersheet as
watch 4
notebook 2
mug 2


in addition, I need it to keep updating based on the number of goals they
meet,
so that if they meet only 3 goals then only the first 3, plus purchases is
totaled, but if they meet 5 goals, then those additional goals get added in
at that time. (hope that makes sense). please help, or point me in the right
direction, if you can. thanks

Sorted appearing table (vlookup, if, concontinate)???
 

I am using excel 2003.
I now have the data devided. though other then for perchase column c always
=1.
I am thinking it needs to be a combo of SumIF and Vlookup. as i am
unfermiallar with SumProduct.
i suppose i am hoping there is a tutorial out there in the world that shows
how that would be used.

"JMB" wrote:

Let's say level is in A1:A8, item description is in B1:B8, and amount is in
C1:C8. Also, assuming that for purchased items, the level number in column A
is either empty or 0.

The level attained is in F1, item description you want totaled is in G1,
try this formula in H1:

=SUMPRODUCT(--($A$1:$A$8<=F1),--($B$1:$B$8=G1),$C$1:$C$8)

Adjust ranges to suit, but prior to XL2007, sumproduct cannot use an entire
column (eg A:A).

It's not clear from your data if the item description and amount are in
separate columns (looks like they may be concatenated together in the same
cell). I would split these - I think it would make it easier to work w/the
data.

Sorted appearing table (vlookup, if, concontinate)???
 

things to mention:
im using excel 2003
some of the items on the list of data, are devided into datavalidation lists.
all = 1

Sorted appearing table (vlookup, if, concontinate)???
 

some good information he
http://xldynamic.com/source/xld.SUMPRODUCT.html






"Luna" wrote:

I am using excel 2003.
I now have the data devided. though other then for perchase column c always
=1.
I am thinking it needs to be a combo of SumIF and Vlookup. as i am
unfermiallar with SumProduct.
i suppose i am hoping there is a tutorial out there in the world that shows
how that would be used.

"JMB" wrote:

Let's say level is in A1:A8, item description is in B1:B8, and amount is in
C1:C8. Also, assuming that for purchased items, the level number in column A
is either empty or 0.

The level attained is in F1, item description you want totaled is in G1,
try this formula in H1:

=SUMPRODUCT(--($A$1:$A$8<=F1),--($B$1:$B$8=G1),$C$1:$C$8)

Adjust ranges to suit, but prior to XL2007, sumproduct cannot use an entire
column (eg A:A).

It's not clear from your data if the item description and amount are in
separate columns (looks like they may be concatenated together in the same
cell). I would split these - I think it would make it easier to work w/the
data.