Sumif & Vlookup (if that is the correct one to use)

[Puggwash63]

hi forum,I have a spreadsheet that has 7 worksheets and 2 of the Worksheets are used to create Order requirements depending on what is entered into the 1st worksheet.How do I use (if correct) the Sumif & Vlookup so when a Date is entered, the cell that shows the Orders looks up the data from the other worksheet what I call Data worksheet.I will try to explain:Worksheet 1 (Data sheet) has orders in each cell which runs from Sun through to Sat (shown in different rows)Worksheet 2 (Slicing sheet) has a Slicing sheet, when the correct date is entered it should look up Worksheet 1 and enter the Order against the cell.Can this be done or do I have to use another formula instead of Sum If & Vlookup?I have forgotten how to post data to the sheet so you can see what I'm after.This is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$83:$B$84,$E$1,Data!$H$83), (Data sheet). This is for SunThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$91:$B$92,$E$1,Data!$H$91), (Data sheet). This is for MonThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$99:$B$100,$E$1,Data!$H$99), (Data sheet). This is for TueThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$107:$B$108,$E$1,Data!$H$107), (Data sheet). This is for WedThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$115:$B$116,$E$1,Data!$H$115), (Data sheet). This is for ThuThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$123:$B$124,$E$1,Data!$H$123), (Data sheet). This is for FriThis is what I have in the cell (Slicing sheet) that looks up the order: =SUMIF(Data!$B$131:$B$132,$E$1,Data!$H$131), (Data sheet). This is for SatOR E1 = the Product chosen if you are wondering.So how do I choose in lets say Cell R64 (Slicing sheet) how to choose either Sun, Mon, Tue, Wed, Thu, Fri or SatPugg

 

[dermie_72]

personally, I only use sumif when there is a chance that there are multiples of the item I am wanting to fine, and the result is numerical. Otherwise I would use Vlookup. Vlookup does have the limitations of only finding 1 return, but it can be alpha and or numeric.

Have you considered a pivot table if there are multiple results for the one criteria?

 

[Puggwash63]

Hi,
Thanks for the reply. All I want to do is to have the correct formula set up so that if I want to choose any dates (Sun-Sat), the sheet will auto find the data from the range.
If anyone could help me with this, I would be in their debt.

 

[Puggwash63]

I hope this sheet can be seen by you all so that it helps make it clear what Im after achieving.

The cell R64 looks up in the Data sheet for the required qty against the cell that is in the Slicing sheet E1 and gives me the correct Qty.
What I have created now is in the Data sheet is 7 Days worth of data that when I choose E1 and place the correct entry it will look up the Data but I do not know how to make it look up the Day asking for so R64 enters the correct Qty.
I hope this makes sense.

Excel 2010
	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
62	Order Fullfilment Reconciliation						Estimate			Actual			Adj +/-
63	A	Burnley Order					1875
64	B	Intercompany Order					26640
65	C	Opening Stock					1500
66	D	Requirement to fullfil order (A+B-C)					27015
67	E	Product produced (during this run only)
68	Closing Stock (E-D)					=
Slicing Sheet

Cell Formulas
Range	Formula
R63	=SUMIF(Data!$B$83:$B$84,$E$1,Data!I83:I84)
R64	=SUMIF(Data!$B$83:$B$84,$E$1,Data!$H$83)
R65	=B44
R66	=(R63+R64)-R65

Excel 2010
	B	C	D	E	F	G	H	I
82	Sun	No. In Bskt	No. In Stk	opening Stock	product produced	Planned Sock	Inter Coy.	Burnley
83	CRUMPET 9's	16	160	400			22,944	4,284
84	CRUMPET 6's	24	240	1500			26,640	1,875
85
86
Data

Cell Formulas
Range	Formula
D83	=C83*10
D84	=C84*10
E83	='Planning Workbook'!E6
E84	='Planning Workbook'!E5
H83	='Planning Workbook'!AE5
H84	='Planning Workbook'!AE6
I83	='Planning Workbook'!L5
I84	='Planning Workbook'!L6

 

[Puggwash63]

Hi,

I wonder if there is someone out there that would be able to assist me with putting this right, so I can progress with the spreadsheet.

For all those who have viewed, many thanks.

Pugg

 

[Puggwash63]

Hi everyone,
how do I post the full spreadsheet on here so that you can see it in its entirety to be able to understand what Im after.
If you have already browsed and seen the above formula's, there are other cells that show where everything is and it may help one of you to conjure some magic for me and show what is needed.

I have updated the sheet to try and work out how to get the different dates which (I think) works but need your help on this one.

Someone please let me know how to get it all across for viewing as it says I can only show 100 columns or rows and when I paste, the machine has an headache as its trying to capture all the data and closes it down.

Pugg

 

[Puggwash63]

Hi,
I have updated the sheet and added sections to get the cells working but I have come across something that I have not seen and maybe one of you may help me understand why it has done this.
Screen below attached.

Excel 2010

	AB	AC	AD
5	Sun	CRUMPET 6's
6
7	22944	Intercompany
8	1875	Burnley
9	1500	Opening Stock

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Slicing Sheet​

Here are the formula's for the below:

Excel 2010
	AB	AC	AD
5	Sun	CRUMPET 6's
6
7	22944	Intercompany
8	1875	Burnley
9	1500	Opening Stock
Slicing Sheet

Cell Formulas
Range	Formula
AB7	=SUMIFS($AD$70:$AD$83,$AC$70:$AC$83,"="&$AB$5:$AC$5,$AE$70:$AE$83,"="&$AC$5)
AB8	=SUMIFS($AG$70:$AG$83,$AF$70:$AF$83,"="&$AB$5:$AC$5,$AH$70:$AH$83,"="&$AC$5)
AB9	=SUMIFS($AJ$70:$AJ$83,$AI$70:$AI$83,"="&$AB$5:$AC$5,$AK$70:$AK$83,"="&$AC$5)

You will see that AB5 has a dropdown to identify what Day you want to show the data.
You will see that AC5 has a dropdown highlighting 2 products.
You will see that AB7:AB9 shows quantities that represent (AC column) the Area that is requiring the quantities.
This sheet shows AB5 has chosen Sun and all 3 cells show quantities.
When I choose any other Day in AB5, AB9 does not pick the quantity required but displays '0'.

Attachment below to show running Mon but AC9 showing '0' when it should show 654.

Excel 2010

	AB	AC	AD
5	Mon	CRUMPET 6's
6
7	1500	Intercompany
8	2420	Burnley
9	0	Opening Stock

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Slicing Sheet​

Here are the formula's for the above:

Excel 2010
	AB	AC	AD
5	Mon	CRUMPET 6's
6
7	1500	Intercompany
8	2420	Burnley
9	0	Opening Stock
Slicing Sheet

Cell Formulas
Range	Formula
AB7	=SUMIFS($AD$70:$AD$83,$AC$70:$AC$83,"="&$AB$5:$AC$5,$AE$70:$AE$83,"="&$AC$5)
AB8	=SUMIFS($AG$70:$AG$83,$AF$70:$AF$83,"="&$AB$5:$AC$5,$AH$70:$AH$83,"="&$AC$5)
AB9	=SUMIFS($AJ$70:$AJ$83,$AI$70:$AI$83,"="&$AB$5:$AC$5,$AK$70:$AK$83,"="&$AC$5)

Can you please explain why AB7 & AB8 display's the quantity shown but AB9 show '0' as it has got me foxed.

Thanks for taking your time to read.

Pugg

 

[Andrew Poulsom]

What's in $AI$70:$AI$83? Why are you using $AB$5:$AC$5 rather than just $AB$5?

 

[Puggwash63]

$AI$70:$AI$83 shows Days.
Im using $AB$5:$AC$5 as these 2 show Day and product

Here is the formula for where the above 3 cells get the info from.

Excel 2010
	AC	AD	AE	AF	AG	AH	AI	AJ	AK
70	Sun	26,640	CRUMPET 9's	Sun	4,284	CRUMPET 9's	Sun	400	CRUMPET 9's
71	Mon	2,500	CRUMPET 9's	Mon	4,500	CRUMPET 9's	Mon	550	CRUMPET 9's
72	Tue	0	CRUMPET 9's	Tue	5,000	CRUMPET 9's	Tue	258	CRUMPET 9's
73	Wed	7,580	CRUMPET 9's	Wed	4,720	CRUMPET 9's	Wed	450	CRUMPET 9's
74	Thu	8,450	CRUMPET 9's	Thu	4,620	CRUMPET 9's	Thu	325	CRUMPET 9's
75	Fri	7,000	CRUMPET 9's	Fri	4,500	CRUMPET 9's	Fri	500	CRUMPET 9's
76	Sat	3,570	CRUMPET 9's	Sat	850	CRUMPET 9's	Sat	2500	CRUMPET 9's
77	Sun	22,944	CRUMPET 6's	Sun	1,875	CRUMPET 6's	Sun	1,500	CRUMPET 6's
78	Mon	1,500	CRUMPET 6's	Mon	2,420	CRUMPET 6's	Mon	654	CRUMPET 6's
79	Tue	2,500	CRUMPET 6's	Tue	0	CRUMPET 6's	Tue	125	CRUMPET 6's
80	Wed	6,500	CRUMPET 6's	Wed	3,500	CRUMPET 6's	Wed	500	CRUMPET 6's
81	Thu	3,540	CRUMPET 6's	Thu	6,400	CRUMPET 6's	Thu	1245	CRUMPET 6's
82	Fri	3,580	CRUMPET 6's	Fri	3,560	CRUMPET 6's	Fri	900	CRUMPET 6's
83	Sat	2,590	CRUMPET 6's	Sat	650	CRUMPET 6's	Sat	1400	CRUMPET 6's
Slicing Sheet

Cell Formulas
Range	Formula
AC70	=Data!$B$82
AC71	=Data!$B$90
AC72	=Data!$B$98
AC73	=Data!$B$106
AC74	=Data!$B$114
AC75	=Data!$B$122
AC76	=Data!$B$130
AC77	=Data!$B$82
AC78	=Data!$B$90
AC79	=Data!$B$98
AC80	=Data!$B$106
AC81	=Data!$B$114
AC82	=Data!$B$122
AC83	=Data!$B$130
AD70	=Data!$H$84
AD71	=Data!$H$92
AD72	=Data!$H$100
AD73	=Data!$H$108
AD74	=Data!$H$116
AD75	=Data!$H$124
AD76	=Data!$H$132
AD77	=Data!$H$83
AD78	=Data!$H$91
AD79	=Data!$H$99
AD80	=Data!$H$107
AD81	=Data!$H$115
AD82	=Data!$H$123
AD83	=Data!$H$131
AF70	=Data!$B$82
AF71	=Data!$B$90
AF72	=Data!$B$98
AF73	=Data!$B$106
AF74	=Data!$B$114
AF75	=Data!$B$122
AF76	=Data!$B$130
AF77	=Data!$B$82
AF78	=Data!$B$90
AF79	=Data!$B$98
AF80	=Data!$B$106
AF81	=Data!$B$114
AF82	=Data!$B$122
AF83	=Data!$B$130
AG70	=Data!$I$83
AG71	=Data!$I$91
AG72	=Data!$I$99
AG73	=Data!$I$107
AG74	=Data!$I$115
AG75	=Data!$I$123
AG76	=Data!$I$131
AG77	=Data!$I$84
AG78	=Data!$I$92
AG79	=Data!$I$100
AG80	=Data!$I$108
AG81	=Data!$I$116
AG82	=Data!$I$124
AG83	=Data!$I$132
AI70	=Data!$B$82
AI71	=Data!$B$90
AI72	=Data!$B$98
AI73	=Data!$B$106
AI74	=Data!$B$114
AI75	=Data!$B$122
AI76	=Data!$B$130
AI77	=Data!$B$82
AI78	=Data!$B$90
AI79	=Data!$B$98
AI80	=Data!$B$106
AI81	=Data!$B$114
AI82	=Data!$B$122
AI83	=Data!$B$130
AJ70	=Data!$J$83
AJ71	=Data!$J$91
AJ72	=Data!$J$99
AJ73	=Data!$J$107
AJ74	=Data!$J$115
AJ75	=Data!$J$123
AJ76	=Data!$J$131
AJ77	=Data!$J$84
AJ78	=Data!$J$92
AJ79	=Data!$J$100
AJ80	=Data!$J$108
AJ81	=Data!$J$116
AJ82	=Data!$J$124
AJ83	=Data!$J$132

 

[Andrew Poulsom]

For me this formula returns 654 with your sample data:

=SUMIFS($AJ$70:$AJ$83,$AI$70:$AI$83,"="&$AB$5,$AK$70:$AK$83,"="&$AC$5)

Check that AI78 and AK78 are exactly the same as B5 and C5 respectively.