Forum Discussion

Chas Wall's avatar
Chas Wall
Copper Contributor
Nov 16, 2017

Count sales of different products to same customer Excel

Hello,

I am trying to get at some cross buying behavior from a large data set that I have. Attached is a simple bit of sample data and then my desired output. There is a ton of data in my actual worksheet so a formula (based off of either the Pivot Table or Sample Data tabs) that would make it easy/efficient to get to the desired output would be great. Thanks very much.

Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Nov 16, 2017

    Chas,

     

    not the best solution.

     

    Define named ranges:

    Brand_A ='Sample Pivot Table'!$C$3:$C$5

    Brand_B ='Sample Pivot Table'!$D$3:$D$5

    Brand_C ='Sample Pivot Table'!$E$3:$E$5

    Brand_D ='Sample Pivot Table'!$F$3:$F$5

     

    And then this formula in C3:

    =SUMPRODUCT(INDIRECT(SUBSTITUTE($B3," ","_"))*INDIRECT(SUBSTITUTE(C$2," ","_")))

     

Resources