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

 

[Gomesy]

Marcelo, I've renamed the ranges to 'Catalog Pricing History' but still get the same results as 0. Everything else seems to make sense in your formula so I just don't understand why it's not working. The SUMPRODUCT formula I've seen takes the two ranges of min/max columns and multiplies them together like =SUMPRODUCT((A2>='Catalog Pricing History'!A:A)*(A2<='Catalog Pricing History'!B:B)*'Catalog Pricing History'!I:I).
Is there any way to incorporate this function, or something like it, into your equation?

 

[Marcelo Branco]

You can do the same, and more efficiently, with SUMIFS
=SUMIFS(Catalog Pricing History'!I:I,'Catalog Pricing History'!A:A,"<="&A2,'Catalog Pricing History'!B:B,">="&A2)

If the formula is returning 0 probably your data have some problem - extraneous spaces?

I did tests with your data sample (post #3) and the formula worked perfectly.
For example,
Row 3: Part # 546326
returned 29.94 (corresponds to row 46 of Catalog sheet)

Row 6: Part # 515034
returned 60.49 (corresponds to row 44 of Catalog sheet)

I don't know why is not working for you.

Check if your dates, in both sheets, are real dates not text. Try this in an empty column
=ISNUMBER(A2)
and copy down

If A2, A3, ...An contain real dates the formulas should return True.

M.