Summary Sum By Week

    I have a huge data set, lets say
    Wk1 Wk2 ...... WkN
    Area1 Type1 Pdt1 Val1
    Area2 Type2 Pdt2 Val2
    Area3 Type1 Pdt3 Val3
    .... ... ...
    .... ... ...
    AreaN Type1 PdtN


    I have to prepare a subset for this table, lets say for Area1, for Weeks 2 to 5 and Products 2 & 3. I need a formula that can pull in the values by looking at area, type, product and weeks.


    I believe match and index can possibly solve this. However I just cann't set it right. If you can help me with a formula that looks up for a value satisfying multiple criteria, that would be great.


    Thanks in advance,


    ET

    Re: Look Up Wimultiple Criteriath


    Hi


    It's not clear to me from your post exactly how your data is layed out (ie where the Wk1, Wk2,....,Wkn appear on your sheet. If at all possible could you post a small sample of your data and include what value you expect a formula to return based on the sample data.


    Thanks


    Richard

    Re: Lookup Based On Column & Row Criteria


    Need more details... also as per the forum guidelines, please don't assume the answer!!!


    I would recommend Pivot table (if the data is spread across worksheets, i will consolidate using pivot table consolidation and then pull out reports based on the same.


    If you can provide a sample dummy worksheet we can solve it quickly

    Thanks: ~Yogendra

    Re: Lookup Based On Column & Row Criteria


    I am not sure if you are summing the values that meet conditions or looking up a single value.


    If the former then have you tried a D SUM? or another form of "D" formula?


    Regards,
    -Dude

    Re: Lookup Based On Column & Row Criteria


    All, thanks for your replies sofar. Sorry that I could not explain my issue properly. So let me give a second shot now.


    I have attached a file which shows the source data and output table. There is no calculation involved, it is just a data pull. Also, I cann't use pivot table here as I have 30 similar worksheets in one file with different source data.

    Re: Lookup Based On Column & Row Criteria


    Here is a worksheet with a solution... If i understand what you are hoping to achieve. essentially a DSUM with dynamic criteria solves your problem. let me recap my understanding. You have a value, i.e. a sale, each value has 3 classifications Type,Territory, and product, respectively. Totaling 8 possible classifications of A-B,T1-3, and P1-3 with many combinations. To the left of that a month. To the right of the 3 classifications are values setup in a step chart formation with weekly increments for that month. You want to pull a value from a database that meets 3 conditions/classifications i.e. A,T1,P1.


    The Dsum works as follows
    =DSUM(A2:G14,4,A19:E20)


    Were the data is in cell A2:G14, the number 4 is the column to pull data from, and row A19:C20, the conditions, Described as follows.....
    ______A______B_______C_________D
    19...Month......Type.....Territory....Product
    20...June 07.......B............T1.............P1



    Second half of solution, dynamic criteria, is in the attachment


    Regards,
    -Dude 8-)

    Re: Lookup Based On Column & Row Criteria


    Thanks for your reply. This seems to be a solution but I haven't got chance to implement it yet. If I run into any troubles, I would buzz you again.


    Thanks again!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login