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

Order Created on	Catalog Version	Customer #	Customer Name	Address	City	ST	Postal Code 5 Digit	Price Condition	M-CAT	S-CAT	Part #	Description	Quantity	GPO Unit Price Paid	GPO Extended Sell Prices	Everyday Unit Price	Everyday Extended Sell Prices
03/18/16	V66							Catalog Price	Plumbing	Shower Doors, Tubs & Enclosures	680085	Seasons Westwind Eng Stn Wall Soap Dsh	8	$24.99	$199.92	$0.00	$0.00
05/18/16	V66							Catalog Price	Plumbing	Water Supply & Fittings	546326	12 SS Faucet Supply Pkg Of 10	11	$28.89	$317.79	$0.00	$0.00
06/22/16	V66							Catalog Price	Appliance	Refrigerators	550131	Seasons 2.4 Cu Ft Rfrgrator ESTAR Black	2	$144.43	$288.86	$0.00	$0.00
07/18/16	V66							Catalog Price	Plumbing	Specialty & Commercial Faucets	500270	AS Chrm Single Pedal Valve Floor Mnt	1	$110.33	$110.33	$0.00	$0.00
07/18/16	V66							Catalog Price	Plumbing	Stems & Cartridges	515034	Moen Tub/Shower Posi-Temp Ctg Plstc	2	$60.49	$120.98	$0.00	$0.00
07/19/16	V66							Catalog Price	Plumbing	Pipe Repair & Weatherization	109113	BlackSwan Plumbers Putty 14 Oz	1	$2.09	$2.09	$0.00	$0.00
07/19/16	V66							Catalog Price	Plumbing	Pop Ups & Repair	515520	Unvrsl SmPlstcw/S/STop P/U Plg 5"L pk/5	2	$10.49	$20.98	$0.00	$0.00
07/19/16	V66							Catalog Price	Plumbing	Toilet & Tank Repair	565110	Sloan Flushmate® PressreAssist Retrofit	1	$139.00	$139.00	$0.00	$0.00
07/19/16	V66							Catalog Price	Plumbing	Toilet & Tank Repair	583305	Sloan Flushmate Cartridge C-100500-K	3	$31.49	$94.47	$0.00	$0.00
07/21/16	V66							Catalog Price	Plumbing	Lavatory Faucets	405388	Seasons Westwind Chrm 1Hndl Bth Fct	1	$73.99	$73.99	$0.00	$0.00
07/21/16	V66							Catalog Price	Janitorial/Cleaning	Cleaning Chemicals	735101	GymWipes Anti-Bacterial Disinfctnt Wipes	1	$59.51	$59.51	$0.00	$0.00
07/22/16	V66							Catalog Price	Floor/Window Coverin	Window Covering Accessories	764283	3/8x36" Wht Fiberglass Baton Rod W/ Adp	18	$8.97	$161.46	$0.00	$0.00
07/22/16	V66							Catalog Price	Floor/Window Coverin	Window Covering Accessories	810527	Unvrsl Baton Adapter Ring 10/Pkg	5	$4.67	$23.35	$0.00	$0.00
07/22/16	V66							Agreement Price	Lighting	Batteries & Flashlights	157755	Duracell Procell AA Alk Battery 24/Pkg	1	$5.70	$5.70	$0.00	$0.00
07/22/16	V66							Agreement Price	Lighting	Fluorescent Tubes	321432	FLR Bulb Phl 25W T8 3500K 85CRI 30pk	1	$101.43	$101.43	$0.00	$0.00
07/22/16	V66							Catalog Price	Hardware	Door Hinges & Closers	893680	4-1/2" Spng Door Hinge Br	10	$12.55	$125.50	$0.00	$0.00
07/22/16	V66							Catalog Price	HVAC	Tools & Supplies	150605	Hydro Balance Power Pac Aerosol Cleaner	18	$6.78	$122.04	$0.00	$0.00
07/22/16	V66							Catalog Price	Lighting	Emergency Lighting Fixtures	310800	Red/Grn Led Exit Sign Battery Backup	2	$70.58	$141.16	$0.00	$0.00
07/22/16	V66							Catalog Price	Lighting	Fluorescent Tubes	311776	FLR Bulb Syl 32W T8 4100K 82CRI 30pk	1	$114.51	$114.51	$0.00	$0.00
07/22/16	V14							Catalog Price	Plumbing	Decorative Bath Hardware	818222	Moen Double SN Robe Hook	5	$4.73	$23.65	$0.00	$0.00
07/22/16	V66							Catalog Price	Plumbing	Toilet & Tank Repair	400446	Kohler Tank Bolt Kit GP52050	2	$9.29	$18.58	$0.00	$0.00
07/22/16	V66							Catalog Price	Plumbing	Toilet & Tank Repair	565119	Sloan Flushmate 503 Tank M-101526-F31	2	$129.27	$258.54	$0.00	$0.00
07/22/16	V66							Agreement Price	Janitorial/Cleaning	Paper Product	117270	Scott Jumbo Roll Bath Tissue 12/Cs	2	$39.95	$79.90	$0.00	$0.00
07/22/16	V14							Catalog Price	Janitorial/Cleaning	Hand Soaps & Sanitizers	117137	1 Gallon GOJO Antbac Hand Soap	2	$10.99	$21.98	$0.00	$0.00
07/22/16	V66							Catalog Price	Floor/Window Coverin	Ceiling Tile	807297	Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn	4	$86.48	$345.92	$0.00	$0.00
07/22/16	V14							Catalog Price	Janitorial/Cleaning	Carpet Extractors & Floor Machines	130741	20 In Blue Floor Pad Package Of 2	1	$46.49	$46.49	$0.00	$0.00
Catalog Pricing History Tab Valid From Valid To Part # Description Base Unit Catalog Version Min QTY Max QTY Price 03/19/16 03/17/17 109113 BlackSwan Plumbers Putty 14 Oz Each V66 12 1,000 $2.39 2/18/2017 2/18/2018 117137 1 Gallon GOJO Antbac Hand Soap Each V15 1 1,000 $13.99 3/18/2017 3/18/2018 117137 1 Gallon GOJO Antbac Hand Soap Each V67 1 1,000 $13.99 3/18/2017 4/8/2018 117137 1 Gallon GOJO Antbac Hand Soap Each H12 1 3 $13.99 02/20/16 02/17/17 117137 1 Gallon GOJO Antbac Hand Soap Each V14 4 1,000 $12.99 03/19/16 03/17/17 117137 1 Gallon GOJO Antbac Hand Soap Each H11 4 1,000 $12.99 03/19/16 03/17/17 117137 1 Gallon GOJO Antbac Hand Soap Each V66 4 1,000 $12.99 2/18/2017 2/18/2018 117137 1 Gallon GOJO Antbac Hand Soap Each V15 4 1,000 $11.99 3/18/2017 3/18/2018 117137 1 Gallon GOJO Antbac Hand Soap Each V67 4 1,000 $11.99 3/18/2017 4/8/2018 117137 1 Gallon GOJO Antbac Hand Soap Each H12 4 1,000 $11.99 03/19/16 03/17/17 117270 Scott Jumbo Roll Bath Tissue 12/Cs Case V66 3 1,000 $55.95 02/20/16 02/17/17 130741 20 In Blue Floor Pad Package Of 2 Package V14 4 1,000 $54.99 03/19/16 03/17/17 130741 20 In Blue Floor Pad Package Of 2 Package H11 4 1,000 $54.99 03/19/16 03/17/17 130741 20 In Blue Floor Pad Package Of 2 Package V66 4 1,000 $54.99 3/18/2017 3/18/2018 150605 Hydro Balance Power Pac Aerosol Cleaner Each V67 1 11 $8.29 03/19/16 03/17/17 150605 Hydro Balance Power Pac Aerosol Cleaner Each V66 12 1,000 $7.74 3/18/2017 3/18/2018 150605 Hydro Balance Power Pac Aerosol Cleaner Each V67 12 1,000 $7.29 2/18/2017 2/18/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package V15 1 1,000 $13.92 3/18/2017 3/18/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package V67 1 1,000 $13.92 3/18/2017 4/8/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package H12 1 11 $13.92 02/20/16 02/17/17 157755 Duracell Procell AA Alk Battery 24/Pkg Package V14 12 1,000 $14.25 03/19/16 03/17/17 157755 Duracell Procell AA Alk Battery 24/Pkg Package H11 12 1,000 $14.25 03/19/16 03/17/17 157755 Duracell Procell AA Alk Battery 24/Pkg Package V66 12 1,000 $14.25 2/18/2017 2/18/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package V15 12 1,000 $12.48 3/18/2017 3/18/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package V67 12 1,000 $12.48 3/18/2017 4/8/2018 157755 Duracell Procell AA Alk Battery 24/Pkg Package H12 12 1,000 $12.48 03/19/16 03/17/17 310800 Red/Grn Led Exit Sign Battery Backup Each V66 1 5 $75.89 03/19/16 03/17/17 310800 Red/Grn Led Exit Sign Battery Backup Each V66 6 1,000 $84.36 03/19/16 03/17/17 310800 Red/Grn Led Exit Sign Battery Backup Each V66 12 1,000 $93.75 3/18/2017 3/18/2018 311776 FLR Bulb Syl 32W T8 4100K 82CRI 30pk Package V67 1 3 $145.63 03/19/16 03/17/17 311776 FLR Bulb Syl 32W T8 4100K 82CRI 30pk Package V66 4 1,000 $145.63 3/18/2017 3/18/2018 311776 FLR Bulb Syl 32W T8 4100K 82CRI 30pk Package V67 4 1,000 $123.13 3/18/2017 3/18/2018 321432 FLR Bulb Phl 25W T8 3500K 85CRI 30pk Package V67 1 3 $165.13 03/19/16 03/17/17 321432 FLR Bulb Phl 25W T8 3500K 85CRI 30pk Package V66 4 1,000 $165.13 3/18/2017 3/18/2018 321432 FLR Bulb Phl 25W T8 3500K 85CRI 30pk Package V67 4 1,000 $141.13 03/19/16 03/17/17 400446 Kohler Tank Bolt Kit GP52050 Each V66 12 1,000 $10.99 03/19/16 03/17/17 405388 Seasons Westwind Chrm 1Hndl Bth Fct Each V66 8 1,000 $81.39 01/13/17 03/17/17 491332 Raio - Face And Body Bar Case Of 500 Case V67 1 1,000 $203.99 01/13/17 03/17/17 491333 Raio - Shampoo Bottle Case Of 144 Case V67 1 1,000 $89.99 01/13/17 03/17/17 491334 Raio - Conditioner Bottle Case Of 144 Case V67 1 1,000 $89.99 01/13/17 03/17/17 491337 Raio- Lotion Tube Case Of 144 Case V67 1 1,000 $91.99 03/19/16 03/17/17 500270 AS Chrm Single Pedal Valve Floor Mnt Each V66 8 1,000 $130.00 03/19/16 03/17/17 515034 Moen Tub/Shower Posi-Temp Ctg Plstc Each V66 1 1,000 $60.49 03/19/16 03/17/17 515520 Unvrsl SmPlstcw/S/STop P/U Plg 5"L pk/5 Package V66 10 1,000 $11.59 03/19/16 03/17/17 546326 12 SS Faucet Supply Pkg Of 10 Package V66 1 1,000 $29.94 02/18/17 05/04/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V15 1 1,000 $161.00 03/18/17 03/26/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V67 1 1,000 $161.00 3/27/2017 3/18/2018 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V67 1 1,000 $99.95 5/5/2017 2/18/2018 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V15 1 4 $99.95 02/20/16 02/17/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V14 5 1,000 $195.00 06/18/16 07/25/16 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V66 5 1,000 $163.48 07/26/16 08/07/16 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V66 5 1,000 $179.00 08/08/16 09/15/16 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V66 5 1,000 $170.00 09/16/16 01/29/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V66 5 1,000 $170.00 01/30/17 03/17/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V66 5 1,000 $195.00 02/18/17 05/04/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V15 5 1,000 $170.00 03/18/17 03/26/17 550131 Seasons 2.4 Cu Ft Rfrgrator ESTAR Black Each V67 5 1,000 $170.00 03/19/16 03/17/17 565110 Sloan Flushmate® PressreAssist Retrofit Each V66 12 1,000 $149.00 3/18/2017 3/18/2018 565119 Sloan Flushmate 503 Tank M-101526-F31 Each V67 1 11 $149.00 03/19/16 03/17/17 565119 Sloan Flushmate 503 Tank M-101526-F31 Each V66 12 1,000 $149.00 3/18/2017 3/18/2018 565119 Sloan Flushmate 503 Tank M-101526-F31 Each V67 12 1,000 $139.00 02/20/16 02/17/17 583305 Sloan Flushmate Cartridge C-100500-K Each V14 12 1,000 $34.49 03/19/16 03/17/17 583305 Sloan Flushmate Cartridge C-100500-K Each V66 12 1,000 $34.49 03/19/16 03/17/17 680085 Seasons Westwind Eng Stn Wall Soap Dsh Each V66 1 1,000 $27.99 03/19/16 03/17/17 735101 GymWipes Anti-Bacterial Disinfctnt Wipes Each V66 4 1,000 $68.99 02/20/16 02/17/17 764283 3/8x36" Wht Fiberglass Baton Rod W/ Adp Each V14 10 1,000 $10.59 03/19/16 03/17/17 764283 3/8x36" Wht Fiberglass Baton Rod W/ Adp Each H11 10 1,000 $10.59 03/19/16 03/17/17 764283 3/8x36" Wht Fiberglass Baton Rod W/ Adp Each V66 10 1,000 $10.59 03/19/16 03/17/17 807297 Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn Carton H11 5 1,000 $102.00 03/19/16 03/17/17 807297 Armstrong 2 X 4' Ceiling Tl 9767 10/Ctn Carton V66 5 1,000 $102.00 03/19/16 03/17/17 810527 Unvrsl Baton Adapter Ring 10/Pkg Package V66 10 1,000 $5.49 02/20/16 02/17/17 818222 Moen Double SN Robe Hook Each V14 10 1,000 $5.59 03/19/16 03/17/17 818222 Moen Double SN Robe Hook Each V66 10 1,000 $5.59 03/19/16 03/17/17 893680 4-1/2" Spng Door Hinge Br Each V66 20 1,000 $14.49 <colgroup><col><col><col><col><col span="3"><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

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.