SUMIFS using Dropdown Menus and Date ranges

J

jjp2985

New Member
Joined
Aug 13, 2021
Messages
12
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
I am trying to put together an inventory tracking workbook/worksheet. I am just starting out and just have 2 lines of data I have to test things out but I can obtain a lot more if it's needed. I am utilizing a few dropdown/Data validations menus within the spreadsheet. In the cell range of Q28:T36 I am trying to come up with a monthly summation for the different departments along with the type of cap. The Departments in that section can be changed based upon a dropdown list and also the type of cap can be changed based upon the dropdown list. I currently have a SUMIFS statement set up to change with each of those and covers the date range of Sept 01, 2021 thru Sept 30, 2021. I am however looking for a way that I would not have to type the dates into the SUMIFS statement like they currently are but utilize the dropdown menu for the Month (cell R29) and Year (cell Q28). So if I were to change the month or year then the data in the table would change accordingly. Is there a way to accomplish this?

OQ Inventory Spreadsheet Demo II.xlsm
ABCDEFGHIJKLMNOPQRSTU
2Product Description:Product Vendor:Product Part Number:
32mL Prep Vial Black Caps: Solid Top, 8-425 Thread, PP,PTFE/F217,100/pkChromtech536008
4GC Vial Caps: Blk Clos. PTFE/SIL/.065"ChromtechCTC-1254
5Department4mL Vial CapsChromtech
6Commercial1 small pack has 100 caps in it1 Large bag has 10 small packs in it
7ResearchDateInitialsDepartmentCap TypePack SizeNumber Pack(s) TookAmount Small Packs TookRemaing 2mL Prep Vial Black Cap Small Packs in InventoryRemaing GC Vial Cap Small Packs in InventoryRemaing 4mL Prep Vial Cap Small Packs in Inventory
8R&D29-Sep-21JJPInventoryGC Vial Cap15361536182311582021 Caps for Vials Allocation
9UK29-Sep-21JJPResearch2mL Prep Black CapLarge Bag5501486182311582mL Prep Black CapGC Vial Cap4mL Vial Cap
10Australia    Commercial000
11Inventory    Research5000
12    R&D000
13    UK000
14    Australia000
15Pack Size    Inventory015360
16Large Bag    
17Small Bag    
18    
19    
20Cap Type    
212mL Prep Black Cap    
22GC Vial Cap    
234mL Vial Cap    
24Test Tube    
25    
26    
27    
28    2021Figuring out General Formula
29    September
30    2mL Prep Black CapGC Vial Cap4mL Vial Cap
31Months    Commercial000
32January    Research5000
33February    R&D000
34March    UK000
35April    Australia000
36May    Inventory015360
37June    
38July    
39August    
40September    
41October    
42November    
43December    
44    
45    
46    
47Years    
482018    
492019    
502020    
512021    
522022    
53    
54    
55    
56Quarters    
57Quarter 1 (Jan-Mar)    
58Quarter 2 (Apr - June)    
59Quarter 3 (July - Sept)    
60Quarter 4 (Oct - Dec)    
61    
Caps for Vials
Cell Formulas
RangeFormula
H9:H61H9=IF(F9="Large Bag",G9*10,IF(F9=""," ",G9))
I9:I61I9=IF(AND(D9="Inventory",E9="2mL Prep Black Cap"),I8+H9,IF(OR(D9="",E9=""),"",IF(E9="2mL Prep Black Cap",I8-H9,I8)))
J9:J61J9=IF(AND(D9="Inventory",E9="GC Vial Cap"),J8+H9,IF(OR(D9="",E9=""),"",IF(E9="GC Vial Cap",J8-H9,J8)))
K9:K61K9=IF(AND(D9="Inventory",E9="4mL Vial Cap"),K8+H9,IF(OR(D9="",E9=""),"",IF(E9="4mL Vial Cap",K8-H9,K8)))
R10:T15R10=SUMIFS($H$8:$H$200,$B$8:$B$200,">=01/01/2021",$B$8:$B$200,"<=12/31/2021",$D$8:$D$200,$Q10,$E$8:$E$200,R$9)
R31:T36R31=SUMIFS($H$8:$H$200,$B$8:$B$200,">=9/01/2021",$B$8:$B$200,"<=9/30/2021",$D$8:$D$200,$W19,$E$8:$E$200,X$18)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I8Cellcontains a blank value textYES
I8Cell Value<100textNO
I9:I202Cellcontains a blank value textYES
I9:I202Cell Value<100textNO
Cells with Data Validation
CellAllowCriteria
Q31:Q36List=$A$6:$A$11
D8List=$A$6:$A$11
E8:E200List=$A$21:$A$24
F8List=$A$16:$A$17
D9:D202List=$A$6:$A$11
F9:F202List=$A$16:$A$17
R9:T9List=$A$21:$A$24
R30:T30List=$A$21:$A$24
Q28List=$A$48:$A$52
R29List=$A$32:$A$43
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi,

try changing this part of SUMIFS in range R31 from “>=9/01/2021” into “>=”&DATE($Q$28, $R$29, 1) and this part “<=9/31/2021” into “<=“&EOMONTH(DATE($Q$28, $R$29, 1) ,0)

Hope this helps
 
Upvote 0
jorismoerings said:
Hi,

try changing this part of SUMIFS in range R31 from “>=9/01/2021” into “>=”&DATE($Q$28, $R$29, 1) and this part “<=9/31/2021” into “<=“&EOMONTH(DATE($Q$28, $R$29, 1) ,0)

Hope this helps
Click to expand...
Hi Joris,

I tried to do that and some other combinations that I've found on the internet and have no luck as everything keeps changing the values to 0 when there should be an answer in them. See below and you can see that now the value that was in S36 has changed to 0 when it should still read 1536

OQ Inventory Spreadsheet Demo II.xlsm
ABCDEFGHIJKLMNOPQRSTU
2Product Description:Product Vendor:Product Part Number:
32mL Prep Vial Black Caps: Solid Top, 8-425 Thread, PP,PTFE/F217,100/pkChromtech536008
4GC Vial Caps: Blk Clos. PTFE/SIL/.065"ChromtechCTC-1254
5Department4mL Vial CapsChromtech
6Commercial1 small pack has 100 caps in it1 Large bag has 10 small packs in it
7ResearchDateInitialsDepartmentCap TypePack SizeNumber Pack(s) TookAmount Small Packs TookRemaing 2mL Prep Vial Black Cap Small Packs in InventoryRemaing GC Vial Cap Small Packs in InventoryRemaing 4mL Prep Vial Cap Small Packs in Inventory
8R&D29-Sep-21JJPInventoryGC Vial Cap15361536182311582021 Caps for Vials Allocation
9UK29-Sep-21JJPResearch2mL Prep Black CapLarge Bag5501486182311582mL Prep Black CapGC Vial Cap4mL Vial Cap
10Australia    Commercial000
11Inventory    Research5000
12    R&D000
13    UK000
14    Australia000
15Pack Size    Inventory015360
16Large Bag    
17Small Bag    
18    
19    
20Cap Type    
212mL Prep Black Cap    
22GC Vial Cap    
234mL Vial Cap    
24Test Tube    
25    
26    
27    
28    2021Figuring out General Formula
29    September
30    2mL Prep Black CapGC Vial Cap4mL Vial Cap
31Months    Commercial000
32January    Research5000
33February    R&D000
34March    UK000
35April    Australia000
36May    Inventory000
37June    
38July    
39August    
40September    
41October    
42November    
43December    
44    
45    
46    
47Years    
482018    
492019    
502020    
512021    
522022    
53    
54    
55    
56Quarters    
57Quarter 1 (Jan-Mar)    
58Quarter 2 (Apr - June)    
59Quarter 3 (July - Sept)    
60Quarter 4 (Oct - Dec)    
61    
Caps for Vials
Cell Formulas
RangeFormula
H9:H61H9=IF(F9="Large Bag",G9*10,IF(F9=""," ",G9))
I9:I61I9=IF(AND(D9="Inventory",E9="2mL Prep Black Cap"),I8+H9,IF(OR(D9="",E9=""),"",IF(E9="2mL Prep Black Cap",I8-H9,I8)))
J9:J61J9=IF(AND(D9="Inventory",E9="GC Vial Cap"),J8+H9,IF(OR(D9="",E9=""),"",IF(E9="GC Vial Cap",J8-H9,J8)))
K9:K61K9=IF(AND(D9="Inventory",E9="4mL Vial Cap"),K8+H9,IF(OR(D9="",E9=""),"",IF(E9="4mL Vial Cap",K8-H9,K8)))
R10:T15R10=SUMIFS($H$8:$H$200,$B$8:$B$200,">=01/01/2021",$B$8:$B$200,"<=12/31/2021",$D$8:$D$200,$Q10,$E$8:$E$200,R$9)
R31:T35,R36,T36R31=SUMIFS($H$8:$H$200,$B$8:$B$200,">=9/01/2021",$B$8:$B$200,"<=9/30/2021",$D$8:$D$200,$W19,$E$8:$E$200,X$18)
S36S36=SUMIFS($H$8:$H$200,$B$8:$B$200,“>=”&DATE($Q$28, $R$29, 1),$B$8:$B$200,“<=“&EOMONTH(DATE($Q$28, $R$29, 1),0),$D$8:$D$200,$W24,$E$8:$E$200,Y$18)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I8Cellcontains a blank value textYES
I8Cell Value<100textNO
I9:I202Cellcontains a blank value textYES
I9:I202Cell Value<100textNO
Cells with Data Validation
CellAllowCriteria
Q31:Q36List=$A$6:$A$11
D8List=$A$6:$A$11
E8:E200List=$A$21:$A$24
F8List=$A$16:$A$17
D9:D202List=$A$6:$A$11
F9:F202List=$A$16:$A$17
R9:T9List=$A$21:$A$24
R30:T30List=$A$21:$A$24
Q28List=$A$48:$A$52
R29List=$A$32:$A$43
 
Upvote 0
Hi,

The data validation in R28 enters the month as a text and not the actual month number.
Try this:
Book1
QRST
282021Figuring out General Formula
29September
302mL Prep Black CapGC Vial Cap4mL Vial Cap
31Commercial000
32Research000
33R&D000
34UK000
35Australia000
36Inventory015360
Sheet1
Cell Formulas
RangeFormula
R31:T36R31=SUMIFS($H$8:$H$201,$B$8:$B$201,">="&DATE($Q$28,MATCH($R$29,$A$32:$A$44,0),1),$B$8:$B$201,"<="&EOMONTH(DATE($Q$28,MATCH($R$29,$A$32:$A$44,0),1),0),$D$8:$D$201,$Q31,$E$8:$E$201,R$30)
Cells with Data Validation
CellAllowCriteria
R29List=$A$32:$A$43
 
Upvote 0
Solution
Hi Joris,

That works. Thanks for all the help on this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
utilizing OFFSET formula inside a formula to transpose list
2
Replies
11
Views
2K
Peter_SSs
Peter_SSs
J
  • Solved
Data Validation reference selection reference other worksheet
Replies
5
Views
544
Fluff
Fluff
P
  • Solved
Calculating shortest to longest distances between Zip Codes (using latitude and longitude)
2 3
Replies
25
Views
929
pawcoyote
P
A
  • Question
create a macro that will replicate the output of my helper columns
Replies
2
Views
423
ajm
A
JenniferMurphy
  • Question
How to update table column properties?
Replies
1
Views
273
jaeiow
J

Forum statistics

Threads
1,214,787
Messages
6,121,561
Members
449,038
Latest member
Guest1337

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top