Can SUMIF & SUMPRODUCT work together in a formula?

[Gomesy]

I have a very complex project that I am working on that involves looking up historical pricing. Here are my challenges:

1. Identical products are found in multiple catalogs and sometimes at a different price.
2. Since pricing within a particular catalog may change occasionally, there are pricing "valid from" and "valid to" dates.
3. Most products have tiered pricing based on quantity breaks.

This is the nature of my project: A customer of mine has 30 hotels and has made over 5000 sales transactions during the past 12 months. During this time the customer was aligned with a GPO and received discounted pricing on all of their purchases. The customer is trying to validate their rationale for being affiliated with the GPO and is now asking how much would the properties have paid if they were not aligned with the GPO and received our "every day" pricing. In other words, what was their price based on their order quantity, catalog they selected and time of year in which their order was placed?

My file has two worksheet tabs. Tab 1 is named "All Sales History". Tab 2 is named "Catalog Pricing History". Here are the field "drivers":

All Sales History Tab

• Column A - Order Date (date field)
• Column B - Catalog Version (alpha-numeric field, i.e.: V66, V65, V14, H12, etc.)
• Column C - Customer Number (numeric field)
• Column L - Part # (unique numeric & alpha-numeric field)
• Column N - Quantity Ordered (numeric field)
• Column Q - Everyday Price Per Unit

Column Q is the field I need populated. Row 1 contains all Header information and all of the data begins on row 2.

Catalog Pricing History Tab

• Column A - Valid From (date field)
• Column B - Valid To (date field)
• Column C - Part # (same as above)
• Column F - Catalog Version (same as above)
• Column G - Minimum Pricing Tier Quantity (numeric value)
• Column H - Maximum Pricing Tier Quantity (numeric value representing the maximum amount you need to purchase in a price tier)
• Column I - Price (numeric value based on you purchased)

I know how to use SUMIF to extract values based on criteria and criteria ranges. I know how to use SUMPRODUCT to extract values that fall within two ranges. I don't know how to "marry" them together.

Can someone write me a formula that can extract the price of a part # on a particular day, from a particular catalog and in a particular quantity please?

Thank you

Rick

 

[Marcelo Branco]

A data sample for testing purposes (~10 rows) along with expected results would be helpful.

As a general idea i think SUMIFS can do what you need.

Maybe something like
=SUMIFS(History!I:I,History!A:A,"<="&A2,History!B:B,">="&A2,History!F:F,B2,History!C:C,L2,History!G:G,"<="&N2,History!H:H,">="&N2)

Hope this helps

M.

 

[Gomesy]

A data sample for testing purposes (~10 rows) along with expected results would be helpful.

As a general idea i think SUMIFS can do what you need.

Maybe something like
=SUMIFS(History!I:I,History!A:A,"<="&A2,History!B:B,">="&A2,History!F:F,B2,History!C:C,L2,History!G:G,"<="&N2,History!H:H,">="&N2)

Hope this helps

Thank you Marcelo. Here are some test rows:

All Sales History Tab

[TABLE="width: 1878"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Order Created on[/TD]
[TD]Catalog Version[/TD]
[TD]Customer #[/TD]
[TD]Customer Name[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]ST[/TD]
[TD]Postal Code 5 Digit[/TD]
[TD]Price Condition[/TD]
[TD]M-CAT[/TD]
[TD]S-CAT[/TD]
[TD]Part #[/TD]
[TD]Description[/TD]
[TD]Quantity[/TD]
[TD]GPO Unit Price Paid[/TD]
[TD]GPO Extended Sell Prices[/TD]
[TD]Everyday Unit Price[/TD]
[TD]Everyday Extended Sell Prices[/TD]
[/TR]
[TR]
[TD]03/18/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Shower Doors, Tubs & Enclosures[/TD]
[TD]680085[/TD]
[TD]Seasons Westwind Eng Stn Wall Soap Dsh[/TD]
[TD]8[/TD]
[TD]$24.99[/TD]
[TD]$199.92[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]05/18/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Water Supply & Fittings[/TD]
[TD]546326[/TD]
[TD]12 SS Faucet Supply Pkg Of 10[/TD]
[TD]11[/TD]
[TD]$28.89[/TD]
[TD]$317.79[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]06/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Appliance[/TD]
[TD]Refrigerators[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]2[/TD]
[TD]$144.43[/TD]
[TD]$288.86[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/18/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Specialty & Commercial Faucets[/TD]
[TD]500270[/TD]
[TD]AS Chrm Single Pedal Valve Floor Mnt[/TD]
[TD]1[/TD]
[TD]$110.33[/TD]
[TD]$110.33[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/18/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Stems & Cartridges[/TD]
[TD]515034[/TD]
[TD]Moen Tub/Shower Posi-Temp Ctg Plstc[/TD]
[TD]2[/TD]
[TD]$60.49[/TD]
[TD]$120.98[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/19/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Pipe Repair & Weatherization[/TD]
[TD]109113[/TD]
[TD]BlackSwan Plumbers Putty 14 Oz[/TD]
[TD]1[/TD]
[TD]$2.09[/TD]
[TD]$2.09[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/19/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Pop Ups & Repair[/TD]
[TD]515520[/TD]
[TD]Unvrsl SmPlstcw/S/STop P/U Plg 5"L pk/5[/TD]
[TD]2[/TD]
[TD]$10.49[/TD]
[TD]$20.98[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/19/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Toilet & Tank Repair[/TD]
[TD]565110[/TD]
[TD]Sloan Flushmate® PressreAssist Retrofit[/TD]
[TD]1[/TD]
[TD]$139.00[/TD]
[TD]$139.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/19/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Toilet & Tank Repair[/TD]
[TD]583305[/TD]
[TD]Sloan Flushmate Cartridge C-100500-K[/TD]
[TD]3[/TD]
[TD]$31.49[/TD]
[TD]$94.47[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/21/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Lavatory Faucets[/TD]
[TD]405388[/TD]
[TD]Seasons Westwind Chrm 1Hndl Bth Fct[/TD]
[TD]1[/TD]
[TD]$73.99[/TD]
[TD]$73.99[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/21/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Janitorial/Cleaning[/TD]
[TD]Cleaning Chemicals[/TD]
[TD]735101[/TD]
[TD]GymWipes Anti-Bacterial Disinfctnt Wipes[/TD]
[TD]1[/TD]
[TD]$59.51[/TD]
[TD]$59.51[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Floor/Window Coverin[/TD]
[TD]Window Covering Accessories[/TD]
[TD]764283[/TD]
[TD]3/8x36" Wht Fiberglass Baton Rod W/ Adp[/TD]
[TD]18[/TD]
[TD]$8.97[/TD]
[TD]$161.46[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Floor/Window Coverin[/TD]
[TD]Window Covering Accessories[/TD]
[TD]810527[/TD]
[TD]Unvrsl Baton Adapter Ring 10/Pkg[/TD]
[TD]5[/TD]
[TD]$4.67[/TD]
[TD]$23.35[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Agreement Price[/TD]
[TD]Lighting[/TD]
[TD]Batteries & Flashlights[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]1[/TD]
[TD]$5.70[/TD]
[TD]$5.70[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Agreement Price[/TD]
[TD]Lighting[/TD]
[TD]Fluorescent Tubes[/TD]
[TD]321432[/TD]
[TD]FLR Bulb Phl 25W T8 3500K 85CRI 30pk[/TD]
[TD]1[/TD]
[TD]$101.43[/TD]
[TD]$101.43[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Hardware[/TD]
[TD]Door Hinges & Closers[/TD]
[TD]893680[/TD]
[TD]4-1/2" Spng Door Hinge Br[/TD]
[TD]10[/TD]
[TD]$12.55[/TD]
[TD]$125.50[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]HVAC[/TD]
[TD]Tools & Supplies[/TD]
[TD]150605[/TD]
[TD]Hydro Balance Power Pac Aerosol Cleaner[/TD]
[TD]18[/TD]
[TD]$6.78[/TD]
[TD]$122.04[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Lighting[/TD]
[TD]Emergency Lighting Fixtures[/TD]
[TD]310800[/TD]
[TD]Red/Grn Led Exit Sign Battery Backup[/TD]
[TD]2[/TD]
[TD]$70.58[/TD]
[TD]$141.16[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Lighting[/TD]
[TD]Fluorescent Tubes[/TD]
[TD]311776[/TD]
[TD]FLR Bulb Syl 32W T8 4100K 82CRI 30pk[/TD]
[TD]1[/TD]
[TD]$114.51[/TD]
[TD]$114.51[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Decorative Bath Hardware[/TD]
[TD]818222[/TD]
[TD]Moen Double SN Robe Hook[/TD]
[TD]5[/TD]
[TD]$4.73[/TD]
[TD]$23.65[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Toilet & Tank Repair[/TD]
[TD]400446[/TD]
[TD]Kohler Tank Bolt Kit GP52050[/TD]
[TD]2[/TD]
[TD]$9.29[/TD]
[TD]$18.58[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Plumbing[/TD]
[TD]Toilet & Tank Repair[/TD]
[TD]565119[/TD]
[TD]Sloan Flushmate 503 Tank M-101526-F31[/TD]
[TD]2[/TD]
[TD]$129.27[/TD]
[TD]$258.54[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Agreement Price[/TD]
[TD]Janitorial/Cleaning[/TD]
[TD]Paper Product[/TD]
[TD]117270[/TD]
[TD]Scott Jumbo Roll Bath Tissue 12/Cs[/TD]
[TD]2[/TD]
[TD]$39.95[/TD]
[TD]$79.90[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Janitorial/Cleaning[/TD]
[TD]Hand Soaps & Sanitizers[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]2[/TD]
[TD]$10.99[/TD]
[TD]$21.98[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Floor/Window Coverin[/TD]
[TD]Ceiling Tile[/TD]
[TD]807297[/TD]
[TD]Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn[/TD]
[TD]4[/TD]
[TD]$86.48[/TD]
[TD]$345.92[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]07/22/16[/TD]
[TD]V14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Catalog Price[/TD]
[TD]Janitorial/Cleaning[/TD]
[TD]Carpet Extractors & Floor Machines[/TD]
[TD]130741[/TD]
[TD]20 In Blue Floor Pad Package Of 2[/TD]
[TD]1[/TD]
[TD]$46.49[/TD]
[TD]$46.49[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]
Catalog Pricing History Tab

[TABLE="width: 677"]
<colgroup><col><col><col><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Valid From[/TD]
[TD]Valid To[/TD]
[TD]Part #[/TD]
[TD]Description[/TD]
[TD]Base Unit[/TD]
[TD]Catalog Version[/TD]
[TD]Min QTY[/TD]
[TD]Max QTY[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]109113[/TD]
[TD]BlackSwan Plumbers Putty 14 Oz[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$2.39[/TD]
[/TR]
[TR]
[TD]2/18/2017[/TD]
[TD]2/18/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V15[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$13.99[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$13.99[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]4/8/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]H12[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]$13.99[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V14[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$12.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]H11[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$12.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$12.99[/TD]
[/TR]
[TR]
[TD]2/18/2017[/TD]
[TD]2/18/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V15[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$11.99[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$11.99[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]4/8/2018[/TD]
[TD]117137[/TD]
[TD]1 Gallon GOJO Antbac Hand Soap[/TD]
[TD]Each[/TD]
[TD]H12[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$11.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]117270[/TD]
[TD]Scott Jumbo Roll Bath Tissue 12/Cs[/TD]
[TD]Case[/TD]
[TD]V66[/TD]
[TD]3[/TD]
[TD]1,000[/TD]
[TD]$55.95[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]130741[/TD]
[TD]20 In Blue Floor Pad Package Of 2[/TD]
[TD]Package[/TD]
[TD]V14[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$54.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]130741[/TD]
[TD]20 In Blue Floor Pad Package Of 2[/TD]
[TD]Package[/TD]
[TD]H11[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$54.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]130741[/TD]
[TD]20 In Blue Floor Pad Package Of 2[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$54.99[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]150605[/TD]
[TD]Hydro Balance Power Pac Aerosol Cleaner[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]$8.29[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]150605[/TD]
[TD]Hydro Balance Power Pac Aerosol Cleaner[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$7.74[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]150605[/TD]
[TD]Hydro Balance Power Pac Aerosol Cleaner[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$7.29[/TD]
[/TR]
[TR]
[TD]2/18/2017[/TD]
[TD]2/18/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V15[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$13.92[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$13.92[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]4/8/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]H12[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]$13.92[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V14[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$14.25[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]H11[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$14.25[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$14.25[/TD]
[/TR]
[TR]
[TD]2/18/2017[/TD]
[TD]2/18/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V15[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$12.48[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$12.48[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]4/8/2018[/TD]
[TD]157755[/TD]
[TD]Duracell Procell AA Alk Battery 24/Pkg[/TD]
[TD]Package[/TD]
[TD]H12[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$12.48[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]310800[/TD]
[TD]Red/Grn Led Exit Sign Battery Backup[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]$75.89[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]310800[/TD]
[TD]Red/Grn Led Exit Sign Battery Backup[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]6[/TD]
[TD]1,000[/TD]
[TD]$84.36[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]310800[/TD]
[TD]Red/Grn Led Exit Sign Battery Backup[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$93.75[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]311776[/TD]
[TD]FLR Bulb Syl 32W T8 4100K 82CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]$145.63[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]311776[/TD]
[TD]FLR Bulb Syl 32W T8 4100K 82CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$145.63[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]311776[/TD]
[TD]FLR Bulb Syl 32W T8 4100K 82CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$123.13[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]321432[/TD]
[TD]FLR Bulb Phl 25W T8 3500K 85CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]$165.13[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]321432[/TD]
[TD]FLR Bulb Phl 25W T8 3500K 85CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$165.13[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]321432[/TD]
[TD]FLR Bulb Phl 25W T8 3500K 85CRI 30pk[/TD]
[TD]Package[/TD]
[TD]V67[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$141.13[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]400446[/TD]
[TD]Kohler Tank Bolt Kit GP52050[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$10.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]405388[/TD]
[TD]Seasons Westwind Chrm 1Hndl Bth Fct[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]8[/TD]
[TD]1,000[/TD]
[TD]$81.39[/TD]
[/TR]
[TR]
[TD]01/13/17[/TD]
[TD]03/17/17[/TD]
[TD]491332[/TD]
[TD]Raio - Face And Body Bar Case Of 500[/TD]
[TD]Case[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$203.99[/TD]
[/TR]
[TR]
[TD]01/13/17[/TD]
[TD]03/17/17[/TD]
[TD]491333[/TD]
[TD]Raio - Shampoo Bottle Case Of 144[/TD]
[TD]Case[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$89.99[/TD]
[/TR]
[TR]
[TD]01/13/17[/TD]
[TD]03/17/17[/TD]
[TD]491334[/TD]
[TD]Raio - Conditioner Bottle Case Of 144[/TD]
[TD]Case[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$89.99[/TD]
[/TR]
[TR]
[TD]01/13/17[/TD]
[TD]03/17/17[/TD]
[TD]491337[/TD]
[TD]Raio- Lotion Tube Case Of 144[/TD]
[TD]Case[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$91.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]500270[/TD]
[TD]AS Chrm Single Pedal Valve Floor Mnt[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]8[/TD]
[TD]1,000[/TD]
[TD]$130.00[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]515034[/TD]
[TD]Moen Tub/Shower Posi-Temp Ctg Plstc[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$60.49[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]515520[/TD]
[TD]Unvrsl SmPlstcw/S/STop P/U Plg 5"L pk/5[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$11.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]546326[/TD]
[TD]12 SS Faucet Supply Pkg Of 10[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$29.94[/TD]
[/TR]
[TR]
[TD]02/18/17[/TD]
[TD]05/04/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V15[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$161.00[/TD]
[/TR]
[TR]
[TD]03/18/17[/TD]
[TD]03/26/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$161.00[/TD]
[/TR]
[TR]
[TD]3/27/2017[/TD]
[TD]3/18/2018[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$99.95[/TD]
[/TR]
[TR]
[TD]5/5/2017[/TD]
[TD]2/18/2018[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V15[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]$99.95[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V14[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$195.00[/TD]
[/TR]
[TR]
[TD]06/18/16[/TD]
[TD]07/25/16[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$163.48[/TD]
[/TR]
[TR]
[TD]07/26/16[/TD]
[TD]08/07/16[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$179.00[/TD]
[/TR]
[TR]
[TD]08/08/16[/TD]
[TD]09/15/16[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$170.00[/TD]
[/TR]
[TR]
[TD]09/16/16[/TD]
[TD]01/29/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$170.00[/TD]
[/TR]
[TR]
[TD]01/30/17[/TD]
[TD]03/17/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$195.00[/TD]
[/TR]
[TR]
[TD]02/18/17[/TD]
[TD]05/04/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V15[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$170.00[/TD]
[/TR]
[TR]
[TD]03/18/17[/TD]
[TD]03/26/17[/TD]
[TD]550131[/TD]
[TD]Seasons 2.4 Cu Ft Rfrgrator ESTAR Black[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$170.00[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]565110[/TD]
[TD]Sloan Flushmate® PressreAssist Retrofit[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$149.00[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]565119[/TD]
[TD]Sloan Flushmate 503 Tank M-101526-F31[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]$149.00[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]565119[/TD]
[TD]Sloan Flushmate 503 Tank M-101526-F31[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$149.00[/TD]
[/TR]
[TR]
[TD]3/18/2017[/TD]
[TD]3/18/2018[/TD]
[TD]565119[/TD]
[TD]Sloan Flushmate 503 Tank M-101526-F31[/TD]
[TD]Each[/TD]
[TD]V67[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$139.00[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]583305[/TD]
[TD]Sloan Flushmate Cartridge C-100500-K[/TD]
[TD]Each[/TD]
[TD]V14[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$34.49[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]583305[/TD]
[TD]Sloan Flushmate Cartridge C-100500-K[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]12[/TD]
[TD]1,000[/TD]
[TD]$34.49[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]680085[/TD]
[TD]Seasons Westwind Eng Stn Wall Soap Dsh[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]1[/TD]
[TD]1,000[/TD]
[TD]$27.99[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]735101[/TD]
[TD]GymWipes Anti-Bacterial Disinfctnt Wipes[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]4[/TD]
[TD]1,000[/TD]
[TD]$68.99[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]764283[/TD]
[TD]3/8x36" Wht Fiberglass Baton Rod W/ Adp[/TD]
[TD]Each[/TD]
[TD]V14[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$10.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]764283[/TD]
[TD]3/8x36" Wht Fiberglass Baton Rod W/ Adp[/TD]
[TD]Each[/TD]
[TD]H11[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$10.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]764283[/TD]
[TD]3/8x36" Wht Fiberglass Baton Rod W/ Adp[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$10.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]807297[/TD]
[TD]Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn[/TD]
[TD]Carton[/TD]
[TD]H11[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$102.00[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]807297[/TD]
[TD]Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn[/TD]
[TD]Carton[/TD]
[TD]V66[/TD]
[TD]5[/TD]
[TD]1,000[/TD]
[TD]$102.00[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]810527[/TD]
[TD]Unvrsl Baton Adapter Ring 10/Pkg[/TD]
[TD]Package[/TD]
[TD]V66[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$5.49[/TD]
[/TR]
[TR]
[TD]02/20/16[/TD]
[TD]02/17/17[/TD]
[TD]818222[/TD]
[TD]Moen Double SN Robe Hook[/TD]
[TD]Each[/TD]
[TD]V14[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$5.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]818222[/TD]
[TD]Moen Double SN Robe Hook[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]10[/TD]
[TD]1,000[/TD]
[TD]$5.59[/TD]
[/TR]
[TR]
[TD]03/19/16[/TD]
[TD]03/17/17[/TD]
[TD]893680[/TD]
[TD]4-1/2" Spng Door Hinge Br[/TD]
[TD]Each[/TD]
[TD]V66[/TD]
[TD]20[/TD]
[TD]1,000[/TD]
[TD]$14.49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

All of the prices being sought in the All Sales History tab are found in the Catalog Pricing tab examples I have provided.

Thank you,

Rick

 

[Marcelo Branco]

Have you tried the formula i suggested above?
Could you provide some (2 or 3) expected results and the logic?

M.

 

[Marcelo Branco]

What is the real name of your second sheet (Catalog)? Adjust the sheet name in my formula.

M.

 

[Gomesy]

What is the real name of your second sheet (Catalog)? Adjust the sheet name in my formula.

Marcelo,

The formula works perfectly!

Thank you so much!

This is going to save me countless hours. I really appreciate it!

Thank you again,

Rick

 

[Gomesy]

Uh-oh Marcelo. It seems the formula isn't working when the Quantity that was ordered on the "All Sales History" tab (Column N) is not found in Min QTY column of the "Catalog Pricing History" tab (Column G).

The logic needs to look between the range of Min QTY (Column N) and Max QTY (Column O) instead of just looking at the value of the Min QTY.

I saw on another site how the SUMPRODUCT formula is used to look between two ranges in two columns. Are you familiar with this formula and can you get it to work in this instance?

 

[Marcelo Branco]

You are welcome. Glad to help

M.

 

[Marcelo Branco]

Uh-oh Marcelo. It seems the formula isn't working when the Quantity that was ordered on the "All Sales History" tab (Column N) is not found in Min QTY column of the "Catalog Pricing History" tab (Column G).

The logic needs to look between the range of Min QTY (Column N) and Max QTY (Column O) instead of just looking at the value of the Min QTY.

I saw on another site how the SUMPRODUCT formula is used to look between two ranges in two columns. Are you familiar with this formula and can you get it to work in this instance?

Have you set the columns min/max properly?
Observe my formula (probably with the wrong sheet name)
=SUMIFS(History!I:I,History!A:A,"<="&A2,History!B:B,">="&A2,History!F:F,B2,History!C:C,L2,History!G:G,"<="&N2,History!H:H,">="&N2)

M.

 

[Marcelo Branco]

The logic needs to look between the range of Min QTY (Column N) and Max QTY (Column O) instead of just looking at the value of the Min QTY.

Adjust the sheet name and correct the columns in my formula to
=SUMIFS(History!I:I,History!A:A,"<="&A2,History!B:B,">="&A2,History!F:F,B2,History!C:C,L2,History!N:N,"<="&N2,History!O:O,">="&N2)

M.