Complicated SUMPRODUCT of multiple items between 2 dates

[Fishboy]

Hi all,

In Excel 2010 I am trying to work out a number of different items sold on a specific date. To make things more complicated, the items being sold sell in differing sized stacks and for different sale values each time. As a result the formula needs to look at "Item Value" from one column, then multiply this by the "Quantity Sold" column, but only IF the sale occurs on a specific date.

An additional complication is that I am recreating the above formula to carry out the calculation on 6 different sales items, and I am looking for have an overall total SUM value for all 6 items on the specific date.

Have I lost you yet? Let me see if I can help visualise the process for you:

'Raw Sales Data'! column A - Item Name (named range Sales_Item)
'Raw Sales Data'! column D - Item Value (named range Sales_ItemValue)
'Raw Sales Data'! column C - Quantity Sold (named range Sales_QuantitySold)
'Raw Sales Data'! column I - Date Of Sale (named range Sales_DateOfSale)
'Background Dates'! B2:AF11 - A grid of dates where each row is a month separated into days

I used the grid of dates so I could drag my formulas around and auto-populate dates into them by cell reference. This means that to perform a "between two dates" calculation I can use...

Sales_DateOfSale,">="&'Background Dates'!B$2,Sales_DateOfSale,"<="&'Background Dates'!C$2

..which in this example would have been B$2=01/03/15 (UK date format) and C$2 = 02/03/15 (UK date format)

So for the first part of my query...

=(SUMPRODUCT(((Sales_Item)="ProductName")*(Sales_QuantitySold)))

...gives me the total number of "ProductName" sold, but not on a specific date. How do I include the date criteria to this calculation.

The next part of my query revolves around...

=SUMIFS(Sales_ItemValue,Sales_Item,"ProductName",Sales_DateOfSale,">="&'Background Dates'!B$2,Sales_DateOfSale,"<="&'Background Dates'!C$2)

...which gives me a nice total of the Item Values between the 2 dates, but it does not take into account the fact that Sales_ItemValue needs to be multiplied by Sales_QuantitySold first.

This leads me on to the final part I am stumped on, which needs to be the above formula applied to 6 items, then a SUM of those 6 results combined.

Am I making things overly complicated? Do I need another hidden table somewhere carrying out the first steps of the calculations and then reference those results in the final formula, or is Excel clever enough to have it all nested in a single, long formula?

Any help offered would be greatly appreciated.

 

[Fishboy]

Ok, so no takers so far it seems. This is driving me crazy as I try a number of different approaches and none of them seem to work.

I've tried

=IF(AND(Sales_DateOfSale=">="&'Background Dates'!I$2,IF(Sales_DateOfSale="<="&'Background Dates'!J$2,((SUMPRODUCT((Sales_Item)=A379)*(Sales_QuantitySold)*(Sales_ItemValue))),"Test")))

Hoping that IF DateOfSale is between the 2 dates, then SUMPRODUCT The correct Sales_Item to multiply Sales_QuantitySold*Sales_ItemValue, but no dice.

I tried

=IF(AND(Sales_DateOfSale>='Background Dates'!I$2,IF(Sales_DateOfSale<='Background Dates'!J$2,IF(Sales_Item=A379,((SUMPRODUCT((Sales_QuantitySold)*(Sales_ItemValue))),"Test"))))

Basically trying the same thing again but including the correct Sales_Item as part of the opening IF statement. Again, no joy here either.

I tried

=SUMIFS((SUMPRODUCT((Sales_QuantitySold)*(Sales_ItemValue))),Sales_Item,A379,Sales_DateOfSale,">="&'Background Dates'!I$2,Sales_DateOfSale,"<="&'Background Dates'!J$2)

I'm sure I've tried at least a dozen other Frankenstien attempts to cobble formulas together but feel like I am banging my head against a brick wall.

Has nobody got any suggestions at all?

 

[Fishboy]

Nevermind, having given up on a formulaic solution I have implemented a workaround where the SUMPRODUCT of Sales_ItemValue and Sales_QuantitySold is calculated in a new column with its own named range on the Raw Sales Data sheet as part of the import macro.

I can now just call on the values of these cells as part of my formulas rather than needing the formulas to try and calculate the SUMPRODUCT as part of the calculation.