sum multiple criteria divided with accumulate value

[koi]

Hi All,


i have one problem how to sum multiple criteria then divided with the accumulate value,


i try to use sumproduct or sumifs but still it wont work when related with accumulate.


i attached work sample here.


column H = is the column i need to work with


now if i pivot all data.. it shows the total 6.35% out of all item which is correct.


but if i choose TYPE as Type 3 then pivot will show -3.32% which is wrong since supposed to be -16.05% as in column N showing (this is because the number supposed to accumulate)


now many of you will say just do sumproduct(TYPE)*(SUBTYPE)*(PERIOD)*Value..


i've done that and the type and sub type if sort will show correct % but if we choose period or total then it will shows wrong figure not 6.35%


any help, idea or anything will appreciate, trying to work this one for like 3 days without any satisfied result


workbook link : http://www21.zippyshare.com/v/12075179/file.html


just click DOWNLOAD NOW in up right corner


thanks All

 

[Hui]

Koi


Try this:

=SUMPRODUCT((B2:B16="type 3")*(C2:C16="sub 1")*(G2:G16))/SUMPRODUCT((B2:B16="type 3")*(C2:C16="sub 1")*(E2:E16))

Then format the cell as %

 

[koi]

hi Hui,


thanks for the advice, i try that and make the cell range as $absolute,


it is working like i said if you choose type 3 in pivot table, the % is correct..


but if you dont choose anything ...then it shows wrong figure.


i think things like this cannot be solve using excel since the variation is :


- total all without any filter in pivot

- pivot Type and choose 1 type there (sub type is unfilter)

- pivot sub type and choose 1 sub there (type is unfilter)


i supposed to figure out another thing and this is to make the chart..so i think i can just pin point to the pivot value..then making chart from that pin point data.

 

[Hui]

I'm unsure exactly what you want as the formula gives you the exact result you wanted without filtering


You may also want to have a read of: http://chandoo.org/wp/2012/06/07/formula-forensics-023/

and associated comments where techniques are discussed for dealing with filtered results.