High level data summary using seperate table to define data to HL code [SOLVED]

[Jack]

Hi everyone, I have a problem I have been trying to solve with SUMPRODUCT but you might have other ideas or come across the same situation.


In summary and in simple terms, I want to use an intermediate conversion table to summarise data:


data -> prod no -> higher level prod grouping (defined in a separate table)


Data in sheet 1 starting at E11

Prod Details

100 $200

101 $230

100 $400

105 $102

101 $130

goes on for 100 lines


In sheet2 I have a table defining the relationship from prod to high level grouping

Prod Desc Higher Level Grouping

100 desc ABC

101 desc DFG

105 desc ABC

and so on at B6:D17 for 12 products


I want to summarise by the Higher level product grouping as follows using the little mapping table above


So answer Summary starting at F34 in sheet2

ABC $702

DFG $350


I have reasons why I can't add the higher level grouping to the data in sheet1 (which is not mine)...plus I find this situation is common for ad hoc reporting and would be a great tool in the toolbox.


Just to illustrate the problem further my formula that isn't working

= SUMPRODUCT((Data!$E$11:$E$100=((B$6:$B$17)*(D6:D27=F34))*(Data!$I$11:$I$1000))


Note it doesn't have to be SUMPRODUCT.


Thanks

John

 

[Smallman]

Hi John


The formula you have put at the bottom is all over the place. Sumproduct needs to operate a bit more smoothly.


Firstly the ranges need to be identical. E11:E100, D11:D100, you tend to compare on peice of criteria for each condition (however you can add more with the curly brackets {} but this is less common.


I will give you a demonstration with hard coded data and your solution needs to follow this format.


Data table on your summary page

Col D Col E COl I

100 ABC $20


So if your summary table has 100 in B6 ABC in C6 then your forumula would look like this


=SUMPRODUCT((Data!$D$11:$D$100=A2)*(Data!$E$11:$E$100=B2)*(Data!I11:$I$100))


Where A2 has 100, B2 has ABC and Col I has the data you are trying to summarise. Notice how the formula is symmetrical and all of the criteria use the same size range. Your logic must basically follow this construct.


Hope this little lesson helped.


Take care


Smallman

 

[Jack]

Hi Smallman


Thanks for your response and time! Actually the different range size 100 vs 1000 was a typo so sorry for that impression as I know from bitter experience SUMPRODUCT has to be generally symmetrical. If I could I would go back and edit this so it doesn't distract anyone. But anyway forget my formula I just thought it might illustrate what I wanted to do better.


My issue which you might not have seen is ABC is not in the data. I can't change the data as it's not mine and so rather than adding it in as a helper each time I am trying to put the higher level "ABC" type association with the product number into a separate small table and use this table in a formula that interrogates the data.


I have thought another practical example of this just to explain this in a different way - say you had sales data with customer names and cities. But you want to do a higher level report in another sheet or workbook that groups cities into sales regions but you can't add this into a column in the data. Instead you want to be able to define the sales groups and cities they contain in a separate table and use this in the formula to group the data by sales region.


Thanks again for the fast response though.

cheers

John

 

[Haseeb A]

Hello John,


Assuming F34 down starting group values ie; ABC,, DFG etc..


So in G34 try this Array formula with CTRL+SHIFT+ENTER


=SUM(SUMIF(Sheet1!E:E,IF(D$6:D$17=F34,B$6:B$17),Sheet1!F:F))


Then copy down.


Haseeb

 

[Jack]

Haseeb


So sorry to take so long to get back to you - been out all day. I will check this out tomorrow and reply again but it looks like very good to me. Thanks very much for taking the time.

Cheers

John

 

[Smallman]

Hi John


The different size ranges I was speaking of was this;


(D6:D27=F34))


That is the dodgy bit not the variance between 100 and 1000.


Have you considered setting up a lookup table for your data. I imagine your data is pretty static month to month (or what ever your reporting cycle is) and because it is not your own you could build a table and add to it continually. So when ever your raw data gets sent to you, you simply apply your lookup formula and you can capture this third criteria instantly. It is a bit like setting up a hierarchy of your very own : )


Take care


Smallman

 

[Jack]

Haseeb your formula indeed works perfectly. This is a little gem of a formula with lots of real work applications, thank you very much again, well thought of.


Thanks again for your interest Smallman agree and I knew it was a bridge too far to introduce two tables to interrogate within the one SUMPRODUCT. Yes I considered the lookup path but there are multiple instances of occurrences of the product numbers with results to sum up and lookups tend to only give you the one targeted result not a summation. So this is why I was going down the SUMPRODUCT route. Haseeb's IF approach was the bit I just hadn't thought of. I am curious if this could still work in SUMPRODUCT for fun.


Kind regards

John

 

[Smallman]

Hi John


It appears you missed my point entirely.


"lookups tend to only give you the one targeted result not a summation"


I was not suggesting you use lookups to get a result. I was suggesting you use a lookup table. This is an entirely different concept to using a Lookup formula. In this way you could add the results from the table to your source data and use formula or a pivot table to interrogate the data.


Anyways I am glad you got a result you can use.


Take care


Smallman