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

#### Puggwash63

##### Board Regular
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

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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?

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.

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
LMNOPQRSTUVWXYZ
63ABurnley Order1875
64BIntercompany Order26640
65COpening Stock1500
66DRequirement to fullfil order (A+B-C)27015
67EProduct produced (during this run only)
68Closing Stock (E-D)=
Slicing Sheet
Cell Formulas
RangeFormula
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
BCDEFGHI
82SunNo. In BsktNo. In Stkopening Stockproduct producedPlanned SockInter Coy.Burnley
83CRUMPET 9's1616040022,9444,284
84CRUMPET 6's24240150026,6401,875
85
86
Data
Cell Formulas
RangeFormula
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

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

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

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
5SunCRUMPET 6's
6
722944Intercompany
81875Burnley
91500Opening Stock

</tbody>
Slicing Sheet
Here are the formula's for the below:

Excel 2010
5SunCRUMPET 6's
6
722944Intercompany
81875Burnley
91500Opening Stock
Slicing Sheet
Cell Formulas
RangeFormula
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
5MonCRUMPET 6's
6
71500Intercompany
82420Burnley
90Opening Stock

</tbody>
Slicing Sheet
Here are the formula's for the above:

Excel 2010
5MonCRUMPET 6's
6
71500Intercompany
82420Burnley
90Opening Stock
Slicing Sheet
Cell Formulas
RangeFormula
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.

Pugg

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

\$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
70Sun26,640CRUMPET 9'sSun4,284CRUMPET 9'sSun400CRUMPET 9's
71Mon2,500CRUMPET 9'sMon4,500CRUMPET 9'sMon550CRUMPET 9's
72Tue0CRUMPET 9'sTue5,000CRUMPET 9'sTue258CRUMPET 9's
73Wed7,580CRUMPET 9'sWed4,720CRUMPET 9'sWed450CRUMPET 9's
74Thu8,450CRUMPET 9'sThu4,620CRUMPET 9'sThu325CRUMPET 9's
75Fri7,000CRUMPET 9'sFri4,500CRUMPET 9'sFri500CRUMPET 9's
76Sat3,570CRUMPET 9'sSat850CRUMPET 9'sSat2500CRUMPET 9's
77Sun22,944CRUMPET 6'sSun1,875CRUMPET 6'sSun1,500CRUMPET 6's
78Mon1,500CRUMPET 6'sMon2,420CRUMPET 6'sMon654CRUMPET 6's
79Tue2,500CRUMPET 6'sTue0CRUMPET 6'sTue125CRUMPET 6's
80Wed6,500CRUMPET 6'sWed3,500CRUMPET 6'sWed500CRUMPET 6's
81Thu3,540CRUMPET 6'sThu6,400CRUMPET 6'sThu1245CRUMPET 6's
82Fri3,580CRUMPET 6'sFri3,560CRUMPET 6'sFri900CRUMPET 6's
83Sat2,590CRUMPET 6'sSat650CRUMPET 6'sSat1400CRUMPET 6's
Slicing Sheet
Cell Formulas
RangeFormula
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
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

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.

Replies
4
Views
101
Replies
4
Views
225
Replies
1
Views
643
Replies
7
Views
190
Replies
1
Views
106

1,203,643
Messages
6,056,520
Members
444,871
Latest member
Vishal Gupta

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back