SUM with multiple (and more than 1) criteria in columns

[ChrisBa]

Hi to everybody!

I need your help for one more time! Here's the situation:


(PLEASE REFER TO ATTACHED FILE FOR A SIMPLIFIED EXAMPLE)
I have a list of sales transactions that includes in the respective columns: customer code, product code, intial price of each product, discount provided to each customer and date invoiced.

Through this period there were some sales promotions during which if each customer purchased in the same date a minimum number of predefined products he woyud get another specific product for free. Each promotion had a set of criteria that included Starting Date, Last Date Active, Set of products that he could choose from, min amount of selected product purchased, id of free product.

I need to evaluate two things for each promotion: which was the total sales amount of the promotion (including the initial price of the free products given) and which was the total value of the free products given with the promotion.

I have included in the attached sheet the correct answers for the simplified example which were manually calculated. In my original file i have a sales data sheet that includes 30,000 rows and manual calculation is impossible.

Any ideas how this can be accomplished through formulas? Thank you all in advance.

 

[Abdul Matheen]

Hi Chris,

I am not just clear about your requirement, could you little more brief about the requirement.

Regards
Abdul Matheen

 

[ChrisBa]

Thanks for the interest!

I'll try to be more precise. If you refer to the attached sheet, you'll see that Customer1 (Cus1) bought in the same day (which is in the days of the promotion) products A & B (which are in the requirements - "at least two of the required list") and so he got product G for free (100% discount). Customer3 (Cus3) also got the promotion because in the same day he purchased products B,C,D (so he fullfilled the product requirement both in terms of choosen products - B and C - but also in terms of quantity (2), so he also got the promotion (he got product G with 100% discount).

So, I need to track all those cases and sum the total sales amount and in the other cell sum the values of the product G that was given for free in the promotion.

I know it's complicated.....

 

[Abdul Matheen]

Hi Chris,

As per my understanding you need the below result.

Initial Sales Amount of the promo (included the init. Price of the free product) 305
Initial Sales Amount of the free product (given with the promo) 35

just i need to know how you arrived to result 305 & 35, means what is the criteria you used to get 305 and 35 from the data.


Regards
Abdul Matheen

 

[ChrisBa]

First i track the transactions from the sales data, in the sense that all the purchases from the same customer in the same date is considered one transaction (i.e. the first 3 rows are the transaction Cus1 made in 5/1/2014).

Then, i check each transaction whether they fullfill the criteria of the promotion (i.e. 1st if the customer bought at least 2 of the required products and 2nd if he received the free product, product G with 100%). This is necessary because as you can see in row G there is customer 4 that got product G with 100% discount but didn't bought the products, this is not included in the promo. Finally, i add the amounts (column C) that i desire (either sum all the inital values of each product that got through the promo or only the values of product G that got 100% and was part of the promo).

I ea-attach the sample file having added some more info based on the above and having coloured the cells that include products sold through the promotion and should be added.

 

[Somendra Misra]

@ChrisBa

Please have a look of attached file. I came up with solution for both the problem with the help of helper columns.

For problem 1: Column G & H are helper column and result is in I31.
For Problem 2: Cell I2 is helper and result in I3.

Just check if this is OK with you.

Regards,

 

[ChrisBa]

@Somendra Misra

Thank you for your effort. I tested your proposed solution in the test file and it seems to work perfectly even with different scenarios (i.e. changing the required products or the number of them, changing the dates). I'll try it in my original files and i'll come back if needed.

It's very interesting how you approached this problem. If i understand coorectly in the first helper column (G) you check whether in each row the customer bought any of the required products and you bring its value.

In the second helper column (H) i'm lost!!!! Would you be kind enough to explain to me the steps there so i could use it in my original file?

The other two helper cells are pretty clear to me.

I believe you propose a trully clever approach. I never could have thought it, it's far beyond my understanding of excel. I really thank you for that.

 

[Somendra Misra]

@ChrisBa

I will hope that solution will be a success in your original file also. And thanks for your kind word.

Now about the approach.
Step 1: for each customer i matched the product if it is in Cumplosary product and if it is than got its price. You could also get a 1 inplace of price. The only purpose of this step is to use it for counting in second step and see if it is more than or equal to 2 which was one of the criteria.

Step 2: count in a expandanble range on customer name if invoice date meeting the criteria and step 1 result is more than 0 (that means the product is in criteria list). So for each customer it will give the count of cumpulsory product if it is in date criteria too. So if total count for each customer meeting the criteria is more than 2 I am placing a 1.
And than finally a simple sumproduct between 1 and prices.

Hope the explantion is clear a little bit.

Regards,