sumifs-match/look up - maybe - Help

[thefencegirl]

Hello, I need some help please, I just can't get the right formula string to make this do what I need it to do. I need column D on the tracking page populated based off of several conditions. Since some items have to be sold together I created a Kit column, not sure if it would actually be required. Any help would be appreciated. Thank you! Sorry for the lay out I can't seem to locate how to post attachments



Data List Page:
Column A Column B Column C Column D
Kit Number Item Numbers Target Price Pay Out
(blank) 55555 $500.00 note: part of Kit 1
1 54123 $1,500.00 $100.00
(blank) 58769 $200.00 note: part of Kit 2
2 57546 $7,500.00 $300.00
3 55543 $40.00 $10.00
4 54752 $2,250.00 $450.00

Tracking Page:
Column A Column B Column C Column D
Kit Number Item Sold Number $Item Sold For Pay out amount note: D Needs to auto populate based off of being greater than target price from Data List Page.

1 54123 $1550.00 ?
0 55555 $ 500.00
0 58769 $ 200.00
2 57546 $7595.00
3 55543 $ 45.00
4 54752 $2225.00

 

[cyrilbrd]

Hi, so the Column A might have blanks? Is this hard coded or the result of a pivot table?

 

[thefencegirl]

Hi, so the Column A might have blanks? Is this hard coded or the result of a pivot table?

Good morning Cyril, Column A may or may not be necessary it is add because I was not sure how to work around that some of the items had to be sold together to receive the pay out

 

[cyrilbrd]

Good morning Cyril, Column A may or may not be necessary it is add because I was not sure how to work around that some of the items had to be sold together to receive the pay out

Ok, it is not clear how the data is setup, maybe a pivot table would suffice for you to get your summary? Have you tried? If yes what does work?

 

[thefencegirl]

ADVERTISEMENT

Ok, it is not clear how the data is setup, maybe a pivot table would suffice for you to get your summary? Have you tried? If yes what does work?

Not sure a pivot table will work as well, I won't be working with this once it is set up and excel skills are very limited. Let's see if this will explain better.

Tracking Log Page

Excel 2007
	A	B	C	D
1	Kit Number	Item Sold Number	Item Sold For	Pay Out Amount
2	1	54123	$ 1,550.00	Needs to Auto Populate based off of the Target Price
3	0	55555	$ 500.00
4	0	58769	$ 200.00
5	2	57546	$ 7,595.00
6	3	55543	$ 45.00
7	4	54752	$ 2,225.00
Tracking Log

Data List Page

Excel 2007
	A	B	C	D
1	Kit Number	Item Numbers	Target Price	Pay Out
2		55555	$ 500.00
3	1	54123	$ 1,500.00	$ 100.00
4		58769	$ 200.00
5	2	57546	$ 7,500.00	$ 300.00
6	3	55543	$ 40.00	$ 10.00
7	4	54752	$ 2,250.00	$ 450.00
Data List

 

[thefencegirl]

Not sure a pivot table will work as well, I won't be working with this once it is set up and excel skills are very limited. Let's see if this will explain better.


Tracking Log Page

Excel 2007
	A	B	C	D
1	Kit Number	Item Sold Number	Item Sold For	Pay Out Amount
2	1	54123	$ 1,550.00	Needs to Auto Populate based off of the Target Price
3	0	55555	$ 500.00
4	0	58769	$ 200.00
5	2	57546	$ 7,595.00
6	3	55543	$ 45.00
7	4	54752	$ 2,225.00
Tracking Log

Data List Page

Excel 2007
	A	B	C	D
1	Kit Number	Item Numbers	Target Price	Pay Out *
2		55555	$ 500.00
3	1	54123	$ 1,500.00	$ 100.00
4		58769	$ 200.00
5	2	57546	$ 7,500.00	$ 300.00
6	3	55543	$ 40.00	$ 10.00
7	4	54752	$ 2,250.00	$ 450.00
Data List

Sorry, there has to be a >= in reference to the Target Price/Sold Price/ and payout

 

[thefencegirl]

Sorry, there has to be a >= in reference to the Target Price/Sold Price/ and payout

Thank you so much for your help all, I managed to make this work with some minor format changes and some different nesting. Have a great weekend!